Query Layer on PostGRES database using Intersection on local shapefile?

JohnathanKool — Thu, 05 Sep 2013 23:36:50 GMT

I have a large amount of data stored in a PostGRES database, that I have been able to access using a Query Layer through ArcMap. My first question is

a) Can the database be spatially queried using a local shapefile - e.g. SELECT FROM <PostGRES Table> WHERE <PostGRES geometry> INTERSECTS <localshapefile located in my C:\Temp>. I know that's not the actual syntax, but the main point is whether I can use a local file to do the querying or if it needs to be loaded into the PostGRES database.

and if this is possible - what would the proper syntax look like? Probably involving ST_INTERSECTS()...

Thanks,

Re: Query Layer on PostGRES database using Intersection on local shapefile?

VinceAngelo — Fri, 06 Sep 2013 10:04:03 GMT

No, you can't use SQL to query a data object which isn't in the database.

Desktop could of course execute such a query, but it would be done in the
ArcGIS execution space (iterating the shapefile rows), and the shapefile
access wouldn't be done with SQL.

If your PG database was enabled as enterprise geodatabase (had ArcSDE
and the SDE.ST_GEOMETRY type installed), then it would be trivial to use
ArcGIS to load the shapefile into PostgreSQL, but without that you'll need
to rely on non-Esri tools like gdal or 'shp2pgsql'.

If the temp table is small enough, you could union the geometries, generate
well-known text from the result, and use that as a WHERE clause constraint
(that would be more efficient than a compound query anyway).

- V

topic Re: Query Layer on PostGRES database using Intersection on local shapefile? in Data Management Questions

Query Layer on PostGRES database using Intersection on local shapefile?

Re: Query Layer on PostGRES database using Intersection on local shapefile?