I am trying find where a line (chosen by a user) intersects any of the multiple polygons within the shapefile. The shapefile is in EPSG:4326 and the coordinates are in (longitude, latitude).

I have loaded a shapefile containing all the buildings (i.e. polygons) from the centre of Rome and have built a schema inside a database with a PostGIS extension (note, PostGIS version: 2.3).

Link to SQL dump and shapefiles used for polygons of my current database set up: https://www.dropbox.com/sh/khszbfwg4lw8l64/AABtnTfgld6EyLwTHwR84ICLa?dl=0

output from my current query '':SELECT ST_AsText(ST_Intersection('LINESTRING (12.497626 41.897156, 12.4922 41.8902)'::geometry, geom::geometry));

output from terminal

I realise I am most certainly at fault here, as I'm no PostGIS expert. However, I'm stuck as 'geom' is a column in the schema, hence, I'm uncertain as to how to proceed.

I have looked at the following similar-ish questions: Intersection of multi-polygons in PostGIS and Intersection between line & polygon in PostGIS? yet, I am still at loss on how to continue.

  • Welcome to GIS SE. As a new user, please take the Tour. You'd probably be well served by reading a primer on spatial types and functions. ST_Intersection is the wrong function for your purpose. You want ST_Intersects in the WHERE clause. – Vince May 10 '18 at 10:49

You must specify the table name in a FROM clause.

Also, you don't need to cast the geom column to geometry (it is already a geometry!) and you want to get a result only for intersecting geometries.

          ST_SetSRID('LINESTRING (12.497626 41.897156, 12.4922 41.8902)'::geometry,4326), 
FROM shp_rome_travelo
WHERE ST_Intersects(
         ST_SetSRID('LINESTRING (12.497626 41.897156, 12.4922 41.8902)'::geometry,4326), 
  • Thanks @JGH this is certainly a step in the right direction, running the query however produces an error: "Operation on mixed SRID geometries". I checked and the geometry is definitely correct, geom column has type: geometry(MultiPolygon, 4326). Regardless, I switched the longs-lats order, and got '0 rows' as the result. Which can't be right, see image: dropbox.com/s/cie3mvbfa0ewqd2/LoS_model_0.png?dl=0 – Gitche May 10 '18 at 11:40
  • @Gitche see edited answer to set the coordinate system – JGH May 10 '18 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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