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: 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.



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.

SELECT ST_AsText(
        ST_Intersection(
          'LINESTRING (12.497626 41.897156, 12.4922 41.8902)'::geometry, 
          geom))
FROM shp_rome_travelo
WHERE ST_Intersects(
        'LINESTRING (12.497626 41.897156, 12.4922 41.8902)'::geometry, 
         geom)
JGH
JGH
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