Skip to main content

I have +30 million records in one PostGIS database, and a few thousand in a GeoPackage, the latter of which are in the same geographical area (a few square kilometres). Is it possible to get the MBR of the GeoPackage and automatically populate the search envelope field of the PostGIS reader?

 

This would make my FeatureJoiner a lot faster.

 

Thanks in advance.

Have you looked into using the FeatureReader for PostGIS connection? You can then send the bounding box from the GeoPackage as the trigger feature.


Have you looked into using the FeatureReader for PostGIS connection? You can then send the bounding box from the GeoPackage as the trigger feature.

Hi David, thanks for this. This does appear to be what I am after, but is there any reason it is so slow? Inputting the min/max x/y in the search envelope is considerably faster. Is there a particular spatial filter on the reader that I should use?


Hi David, thanks for this. This does appear to be what I am after, but is there any reason it is so slow? Inputting the min/max x/y in the search envelope is considerably faster. Is there a particular spatial filter on the reader that I should use?

Difficult to say without knowing more. Does the PostGIS table have a spatial index? How complex is the trigger polygon (number of vertices)? Does the trigger polygon and the PostGIS table have the same coordinate system?


Apologies if this is obvious, but you'd want to make sure you're only sending one feature from the GeoPackage to the PostGIS FeatureReader. Otherwise you're making multiple queries.

So use the BoundingBoxAccumulator transformer before the FeatureReader.

The other alternative is to put the PostGIS reader into a separate workspace. Read the GeoPackage in one workspace, use the BoundingBoxAccumulator then a BoundsExtractor to get the extents as coordinates. Then use a WorkspaceRunner to run the PostGIS workspace, passing in the bounds as parameters.

I'd be interested to know if that's quicker. I suppose the difference is that the FeatureReader is using a spatial overlay command in PostGIS, whereas the extents are using a simpler where clause (i.e. they're just using the numbers, not the geometry). I don't know if that's true, but if so it might account for a speed difference. I'll ask our developers about that.


Reply