1

I have two tables:

  1. roads containing one row (line) for each road with column road_code uniquely identifying the name of the road;
  2. districts containing one row (polygon) for each district with column district_code uniquely identifying each district.

I want to create a new table of lines called roads_by_district, where each road from the table roads is parted based on the intersection with the districts.

So table roads_by_district should look like

road_code | district_code | geom(line)
H1          District_A      0105...
H2          District_A      0105...
H1          District_B      0105...
H2          District_B      0105...

I guess I should use a query with ST_Intersects and ST_Contains but I don't want to return the entire road H1 that partially intersects with district District_A, but only the part of H1 that is totally contained by District_A.

1

I think I found my query:

CREATE TABLE roads_by_district AS
SELECT a.road_id as road_id, a.comune_id as comune_id,
       ST_Intersection(a.geomline, a.geompoly) as geom
FROM (SELECT roads.road_code as road_id, comuni.pro_com As comune_id,
             roads.geom as geomline, districts.geom as geompoly
       FROM roads INNER JOIN districts
       ON (ST_Intersects(roads.geom, districts.geom, 4326))) a;

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.