Creating intersects in PostGIS -- chitawan example

intersects.sql

#### getting intersects of roads from a district. To eliminate bordering things using ST_Contains

DROP TABLE chitawan_roads_intersect;

CREATE TABLE chitawan_roads_intersect
WITH (OIDS)
AS
	SELECT osm_roads_4326.*
	FROM osm_roads_4326, districts
		WHERE
		ST_Intersects((SELECT geom FROM districts WHERE districts.ptdist_nam = 'Chitawan'),osm_roads_4326.way);

select * from chitawan_roads_intersect;

### quickly creating a unique id for each row and setting it as the primary key

ALTER TABLE chitawan_roads_intersect ADD COLUMN pkey bigserial; 
ALTER TABLE chitawan_roads_intersect ADD PRIMARY KEY (pkey);

### Deleting duplicates

DELETE FROM chitawan_roads_intersect
WHERE pkey IN (SELECT pkey
              FROM (SELECT pkey,
                             row_number() over (partition BY osm_id, way ORDER BY pkey) AS rnum
                     FROM chitawan_roads_intersect) t
              WHERE t.rnum > 1);

### Deleting the admin boundaries from the results 

DELETE FROM chitawan_roads_intersect WHERE chitawan_roads_intersect.boundary = 'administrative';