2

So I have a table in PostGIS, and each row has a linestring geometry in it. I would like to do a query to return the whole rows of the table that intersect with the linestring along with the position(s) of that intersect.

At the moment I am using;

import geopandas as gpd
import psycopg2  # (if it is postgres/postgis)

con = psycopg2.connect(database="your database", user="user", password="password",
    host="your host")

sql = "select geom, x,y,z from your_table which intersects Linestring”

df = gpd.GeoDataFrame.from_postgis(sql,con,geom_col='geom' )

I’m a complete beginner to SQL and the resources surround PostGIS ST_Intersect aren't so helpful.

3

I think this query should work,

sql = (
'SELECT geom, x, y, z, ST_Intersection(geom, {1}) '
'FROM your_table '
'WHERE ST_Intersects({1}, geom)'
).format(your_geometry)

Now, you don't specify the format of your geometry so you will have to convert it to postgis geometry somehow. You have a number of functions to read a geometry, like ST_GeomFromGeoJSON, ST_GeomFromText, etc.

ST_Intersects returns true if the geometrys share any portion of space ST_Intersects Doc

ST_Intersection returns the geometry result of the intersection ST_Intersection Doc

| improve this answer | |

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.