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.
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?
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
FME_SQL_DELIMITER ;
SELECT * FROM table_A WHERE file_num = $mSheet1]
Error:
Executing custom data query: 'SELECT * FROM table_A
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
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)
Line 1 FME_SQL_DELIMITER ;
Line 2 SELECT * FROM table_A
Line 3 WHERE 'file_num' = '$&Sheet1]'
Thank you.