Hello, Fairly new to FME. I have a postgis table and I would like to extract records WHERE.... fieldA = the records from an excel sheet column, then take those records and convert them to a shapefile. I can do this manually using ArcGIS or QGIS using a query to query out these records but how can I streamline this process using FME? Thank you.

Extracting records from postgis using excel sheet as condition then have a shapefile as output

Is there any field of that Excel that could work as the Shapefile name? It should be unique for every row of the Excel.

If so:

- Read the Excel with the Excel Reader.

- Use SQLExecutor transformer to execute SQL sentences against your PostGis database. Merge initiator attributes with the result.

- Use the Shape Writer to write the result. As the name of the Shape, use the field of the Excel with the unique value for every row.

I thought the shapefile name can be anything I want it to be when using a writer as an output. I'm stuck on how to do this process using FME. Do I use two readers, one to read postgis table and another reader to read from columnA from the excel sheet? Combine them and from the records I get, generate a shapefile form the records extracted from the postgis table?

I just read the rest of your comment now. I will give that a try. Thank you.

I thought the shapefile name can be anything I want it to be when using a writer as an output. I'm stuck on how to do this process using FME. Do I use two readers, one to read postgis table and another reader to read from columnA from the excel sheet? Combine them and from the records I get, generate a shapefile form the records extracted from the postgis table?

I understood that you want to create as many different shape files as WHERE clauses in the Excel file. Is that right? Or do you want to store all the results in the same shape file?

In any way. You only need the Excel Reader and the SQLExecutor transformer to obtain the data from PostGis.

Sorry, I was side tracked, I had time to go back to this again. So, I now have a Fatal Errors when running the workbench. In the SQLExecutor transformer, I have my connection to the db and for "SQL Statement:" I have the following:

FME_SQL_DELIMITER ;

SELECT FROM table_A
WHERE file_num = $ Sheet1]

//$/Sheet1] is the name I gave to the Excel reader. I also has $(Sheet1)

Error I get is:

Error executing SQL command ('declare "public_custom_query_crsr" cursor for SELECT FROM table_A

WHERE file_num = $ Sheet1]'): 'ERROR: syntax error at or near "$"

LINE 2: WHERE file_num = $ Sheet1]

^

'

A fatal error has occurred. Check the logfile above for details

Successfully closed POSTGIS database reader

Sorry, I was side tracked, I had time to go back to this again. So, I now have a Fatal Errors when running the workbench. In the SQLExecutor transformer, I have my connection to the db and for "SQL Statement:" I have the following:

FME_SQL_DELIMITER ;

SELECT FROM table_A
WHERE file_num = $ Sheet1]

//$/Sheet1] is the name I gave to the Excel reader. I also has $(Sheet1)

Error I get is:

Error executing SQL command ('declare "public_custom_query_crsr" cursor for SELECT FROM table_A

WHERE file_num = $ Sheet1]'): 'ERROR: syntax error at or near "$"

LINE 2: WHERE file_num = $ Sheet1]

^

'

A fatal error has occurred. Check the logfile above for details

Successfully closed POSTGIS database reader

Correction: This is what I have in the "SQL Statement:"

FME_SQL_DELIMITER ;

SELECT * FROM table_A WHERE file_num = $mSheet1]

Error:

Executing custom data query: 'SELECT * FROM table_A

WHERE file_num = $eSheet1]'

Error executing SQL command ('declare "public_custom_query_crsr" cursor for SELECT * FROM table_A

WHERE file_num = $iSheet1]'): 'ERROR: syntax error at or near "$"

LINE 2: WHERE file_num = $fSheet1]

^

'

A fatal error has occurred. Check the logfile above for details

Successfully closed POSTGIS database reader

Sorry, I was side tracked, I had time to go back to this again. So, I now have a Fatal Errors when running the workbench. In the SQLExecutor transformer, I have my connection to the db and for "SQL Statement:" I have the following:

FME_SQL_DELIMITER ;

SELECT FROM table_A
WHERE file_num = $ Sheet1]

//$/Sheet1] is the name I gave to the Excel reader. I also has $(Sheet1)

Error I get is:

Error executing SQL command ('declare "public_custom_query_crsr" cursor for SELECT FROM table_A

WHERE file_num = $ Sheet1]'): 'ERROR: syntax error at or near "$"

LINE 2: WHERE file_num = $ Sheet1]

^

'

A fatal error has occurred. Check the logfile above for details

Successfully closed POSTGIS database reader

Could you share a sample of your Excel file? Maybe that way we could guide you better.

So I have a table in Postgis with about 10 different columns and one of the columns is named file_num (character) with about 200k records with values LAX140127, TRE160056, etc... and I have an excel sheet with one column named File_Num of approx 80 records. These 80 records in the Excel sheet are the ones I want to extract from the table by using the excel sheet.

Sample Excel Sheet

sampleexcelsheet.png

After looking at your Excel sheet, from the Excel Reader you must have an attribute called "File_Num".

The following SQL should work:

FME_SQL_DELIMITER ;

SELECT * FROM table_A WHERE file_num = @Value(File_Num)

Maybe you will need to add the database schema to the table. Not sure about that.

In the SQLExecutor you will need to expose the attributes from the PostGIS database.

Add the SHAPE Writer. Use a fanout for the name of the file using again @Value(File_Num)

Could you share a sample of your Excel file? Maybe that way we could guide you better.

Thanks @Oscard. I found what I was doing wrong. It was the syntax. I was missing quotes on the query on Line 3 : I changed it to the following and it works now.

Line 1 FME_SQL_DELIMITER ;

Line 2 SELECT * FROM table_A

Line 3 WHERE 'file_num' = '$&Sheet1]'

Thank you.

Reply

Community Stats

Reply

Community Stats

Sign up

An FME Account is required to contribute

Login to the community

An FME Account is required to contribute

Scanning file for viruses.

This file cannot be downloaded