1

Scenario:

[A] 5,784 polygon geometries + [B] 2,100,000 address points

Source Format: [A] GDB Feature Class, [B] PostGIS Table

Goal:

Find which address points [B] intersect with polygon geometries [A]

Workaround Attempts + Question:

[Cannot use SpatialRelator/SpatialFilter, would have to read in all 6 million points first, then use these as the Supplier/Filter - would be very slow, takes 15 mins to read etc before any spatial processing]

I have used an address points [A] FeatureReader, using the [B] polygon geometries as the Initiator, using WHERE 'Initiator OGC-Intersects Result'. This took 40 minutes to process.

Here is an example of the log while the FeatureReader is running;

2022-08-20 21:00:14|   1.2|  0.0|INFORM|Notice - Performing envelope query on the `POSTGIS' reader, post-filtering spatial predicate in FME
2022-08-20 21:00:14|   1.2|  0.0|INFORM|The minimum (x,y) and maximum (x,y) bounds, respectively, of the search envelope being used are: (1346681.6345964964, 4864224.784811478) , (1346852.7337965071, 4864544.631611481)
2022-08-20 21:00:15|   1.2|  0.0|INFORM|Executing data query: 'declare "public_nz_street_address_crsr" cursor for select "id","address_id","change_id","address_type","unit_value","address_number","address_number_suffix","address_number_high","water_route_name","water_name","suburb_locality","town_city","full_address_number","full_road_name","full_address","road_section_id","gd2000_xcoord","gd2000_ycoord","water_route_name_ascii","water_name_ascii","suburb_locality_ascii","town_city_ascii","full_road_name_ascii","full_address_ascii", ST_AsEWKB("geom") as "geom" from "public"."nz_street_address" where "geom" && ST_GeomFromText('POLYGON((1346681.6345964964 4864224.784811478, 1346681.6345964964 4864544.631611481, 1346852.7337965071 4864544.631611481, 1346852.7337965071 4864224.784811478, 1346681.6345964964 4864224.784811478))', 2193)'
2022-08-20 21:00:15|   1.2|  0.0|INFORM|Reading POSTGIS table: 'public.nz_street_address'...

I attempted to make a quicker workaround by using an [A] address points SQLExecutor [PostGIS Format] using the [B] polygon geometries as the Initiator.

In addition I exposed the [B] polygon geometries as the Initiator, using GeometryExtractor, but having issues running the code, this is my best attempt so far in the SQL Statement Widget - this errors out as not recognized.

SQLExecutor Screenshot

How can I get the following SQL query statement to work in SQLExecutor Transformer?

select address_id, full_address, geom
from "public"."nz_street_address" as addy,
ST_WKBToSQL(@Value(lom_geometry)) as activity_geom
where ST_Intersects(addy.geom, activity_geom)

Above SQL Query Error in FME:

2022-08-21 20:30:46|   1.3|  0.0|INFORM|Executing custom data query: 'select address_id, full_address, geom
from "public"."nz_street_address" as addy,
ST_WKBToSQL(01030000000100000007000000104D3A81306934413178B4F83D9352419A873BFE236934416C89EAC43E935241AEED327351693441660CABCF6B9352410BAE8E93386A3441CFD118F45D935241FD80DCA5386A3441EBBCFBF25D935241AD8F7849096A3441BE28052830935241104D3A81306934413178B4F83D935241) as activity_geom
where ST_Intersects(addy.geom, activity_geom)'
NOTICE:  identifier "d3a81306934413178b4f83d9352419a873bfe236934416c89eac43e935241aeed327351693441660cabcf6b9352410bae8e93386a3441cfd118f45d935241fd80dca5386a3441ebbcfbf25d935241ad8f7849096a3441be28052830935241104d3a81306934413178b4f83d935241" will be truncated to "d3a81306934413178b4f83d9352419a873bfe236934416c89eac43e935241ae"
2022-08-21 20:30:46|   1.3|  0.0|WARN  |Error executing SQL command ('declare "public_custom_query_crsr" cursor for select address_id, full_address, geom
from "public"."nz_street_address" as addy,
ST_WKBToSQL(01030000000100000007000000104D3A81306934413178B4F83D9352419A873BFE236934416C89EAC43E935241AEED327351693441660CABCF6B9352410BAE8E93386A3441CFD118F45D935241FD80DCA5386A3441EBBCFBF25D935241AD8F7849096A3441BE28052830935241104D3A81306934413178B4F83D935241) as activity_geom
where ST_Intersects(addy.geom, activity_geom)'): 'ERROR:  syntax error at or near "D3A81306934413178B4F83D9352419A873BFE236934416C89EAC43E935241AEED327351693441660CABCF6B9352410BAE8E93386A3441CFD118F45D935241FD80DCA5386A3441EBBCFBF25D935241AD8F784909...'
2022-08-21 20:30:46|   1.3|  0.0|ERROR |A fatal error has occurred. Check the logfile above for details

Additional Side Note:

[A] + [B] As two tables in a PostGIS database with a very similar query completes in 00:00:00.891 seconds.

select address_id, full_address, geom
from "public"."nz_street_address" as addy,
"public"."example_polygons" as activities
where ST_Intersects(addy.geom, activities.geom)

8:44:09 PM Started executing query at Line 29
(1359 row(s) affected)
Total execution time: 00:00:00.891
6
  • Your Question is missing a question.
    – Vince
    Aug 20, 2022 at 9:26
  • Added: How can I get this SQL query to work?
    – Rose
    Aug 20, 2022 at 9:46
  • Your title is the only place the error is mentioned, and it's not clear how you generated it. The Question would be clearer if the question is at the end .
    – Vince
    Aug 20, 2022 at 9:57
  • Realized I've made some typos and tried to clarify the question / error as much as I could.
    – Rose
    Aug 21, 2022 at 8:49
  • Since 5k polygons is tiny, and the larger table is already in PostgreSQL, and it's really fast in PostgreSQL, why not just use PostgreSQL?
    – Vince
    Aug 21, 2022 at 13:03

1 Answer 1

0

1st Step

FME Feature Attribute Value lom_geometry needs to be encapsulated in commas and given a spatial reference as extracting the fme_geometry from a GDB, means that no spatial reference is given for that particular geometry.

select address_id, full_address, geom
from "public"."nz_street_address" as addy
where ST_Intersects(addy.geom, ST_GeomFromWKB('@Value(lom_geometry)',2193))

2nd Step

The above code throws an error - ERROR:Invalid endian flag value encountered.. This is a result of the ST_GeomFromWKB() function, a workaround would be using ST_GeometryFromText() in the following sql query.

select address_id, full_address, geom
from "public"."nz_street_address" as addy
where ST_Intersects(addy.geom, ST_GeomFromText('@Value(lom_geometry)',2193))

The query above however unfortunately takes approximately 40 mins. In this specific content PostGIS > FME for speeds.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.