Line intersection with multiple polygons from a shapefile in PostGIS/PostgreSQL database

by Gitche   Last Updated May 10, 2018 11:22 AM

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:

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.

Answers 1

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.

          'LINESTRING (12.497626 41.897156, 12.4922 41.8902)'::geometry, 
FROM shp_rome_travelo
WHERE ST_Intersects(
        'LINESTRING (12.497626 41.897156, 12.4922 41.8902)'::geometry, 
May 10, 2018 11:10 AM

Related Questions

Rhumb lines intersection

Updated December 15, 2017 09:22 AM

r - intersection of 3D objects

Updated September 23, 2018 20:22 PM

line-polygon intersection with tolerance

Updated September 03, 2018 13:22 PM