I have point table and polygon table in PostgreSQL / PostGIS
The polygon table is a series of concentric polygons getting ever smaller / nearest the point See Picture
Each point could be in 1 or many polygons
Within this table is a level column
How do I perform a spatial query returning the join between the polygon contains the point where MAX(level) ?
The result I’m expecting is 1 polygon per point (A polygon can contain 1 or more points)
What you could do is use a Spatial Relator with the point being hte requestor the polys being the supplier. You can configure the transformer to generate a list on the output (which will contain the polygon id and level). Then sort the list using a ListSorter so the max level is element 0. Then you can use that value in a FeatureMerger to grab the correct polygon
What you could do is use a Spatial Relator with the point being hte requestor the polys being the supplier. You can configure the transformer to generate a list on the output (which will contain the polygon id and level). Then sort the list using a ListSorter so the max level is element 0. Then you can use that value in a FeatureMerger to grab the correct polygon
@hkingsbury Can you clarify as I’m not getting the expected results
I attach screen shots of workspace and transformers also attach the workspace
SpatialRelator see pic
List Name _max_adm
Selected attributes level + id: Is there any other settings here?
ListSorter
List Attributes _max_adm{}.adm_level
Sort Type: Numeric
Sort Order: Descending
If I understand correctly this should give the maximum level at position 0?
FeatureMerger
Requestor is output from ListSorter osm_id
Supplier is from original Polygon table _max_adm{0}.osm_id
I also tried this with the table id column, didn’t make a difference
My test data has 45 points all of these are coming out of FeatureMerger as Unused Supplier
To reduce the point data I’ve used a WHERE clause I’m unable to this on the polygon data without adding another FME spatial transformer
Am I missing something?
