Skip to content

Latest commit

 

History

History
128 lines (109 loc) · 4.34 KB

SQL_Queries.org

File metadata and controls

128 lines (109 loc) · 4.34 KB

Filter Hexagon grid to only those in area

SELECT gis.squares_l1_05m.* – filter hexagons contained by ccl1 FROM gis.ccl1 LEFT JOIN gis.squares_l1_05m ON ST_Contains(gis.ccl1.geom, gis.squares_l1_05m.geom) – filter hexagons out hexagon that intersects ccl1_pits LEFT JOIN gis.ccl1_pits ON ST_Intersects(gis.ccl1_pits.geom, gis.squares_l1_05m.geom) WHERE gis.ccl1_pits.gid IS NULL AND gis.squares_l1_05m.project_no != -1

New select

SELECT project_no, ST_AsGeoJson(ST_Union(geom)) FROM gis.squares WHERE gis.squares.project_no != ‘-1’ group by project_no

Anoterh select

SELECT project_no, project_name, company, length, width, height, number_of_power_points_needed, ST_AsGeoJson(ST_Union(geom)) FROM gis.squares FULL OUTER JOIN escdummy ON project_no = id WHERE gis.squares.project_no != ‘-1’ group by project_no, project_name, company, length, width, height, number_of_power_points_needed

NOT DONE : Return T/F based on contains

SELECT * FROM gis.ccl1 LEFT JOIN gis.squares_l1_05m ON ST_Contains(gis.ccl1.geom, gis.squares_l1_05m.geom) RIGHT JOIN gis.ccl1_pits ON ST_Contains(gis.ccl1_pits.geom, gis.squares_l1_05m.geom)

Clear all project_no back to -1

UPDATE gis.squares_l1_05m SET project_no = -1

Update selected column

UPDATE gis.squares_l1_05m SET project_no = 1 WHERE a = 0 AND b = 0

SELECT all

SELECT * FROM gis.squares_l1_05m ORDER by gid

SELECT stuff touching the point

SELECT gis.squares_l1_05m.geom – filter hexagons contained by ccl1 FROM gis.ccl1 LEFT JOIN gis.squares_l1_05m ON ST_Contains(gis.ccl1.geom, gis.squares_l1_05m.geom) AND (ST_Intersects( ST_MPolyFromText(‘MULTIPOLYGON((( 103.963066660790901 1.340752667639772, 103.963069254941715 1.34075718931442 , 103.963074442767294 1.340757189037779, 103.96307703644203 1.340752667086492, 103.963074442291202 1.340748145411852, 103.963069254465651 1.340748145688492, 103.963066660790901 1.340752667639772 )))’, 4326), gis.squares_l1_05m.geom))

– filter hexagons out hexagon that intersects ccl1_pits LEFT JOIN gis.ccl1_pits ON (ST_Intersects(gis.ccl1_pits.geom, gis.squares_l1_05m.geom)) WHERE gis.ccl1_pits.gid IS NULL

Select touching point test

SELECT gis.squares_l1_05m.geom – filter hexagons contained by ccl1 FROM gis.ccl1 LEFT JOIN gis.squares_l1_05m ON ST_Contains(gis.ccl1.geom, gis.squares_l1_05m.geom) AND (ST_Intersects( ST_GeomFromText(‘POINT(103.9629548 1.34095539)’,4326), gis.squares_l1_05m.geom))

– filter hexagons out hexagon that intersects ccl1_pits – this filters out the boundaries LEFT JOIN gis.ccl1_pits ON (ST_Intersects(gis.ccl1_pits.geom, gis.squares_l1_05m.geom)) WHERE gis.ccl1_pits.gid IS NULL

SELECT (GEOMFROM TEXT)

Mutlipolygon (hexagon?)

SELECT ST_MPolyFromText(‘MULTIPOLYGON((( 103.963066660790901 1.340752667639772, 103.963069254941715 1.34075718931442 , 103.963074442767294 1.340757189037779, 103.96307703644203 1.340752667086492, 103.963074442291202 1.340748145411852, 103.963069254465651 1.340748145688492, 103.963066660790901 1.340752667639772 )))’, 4326);

Point

SELECT ST_GeomFromText(‘POINT( 103.963133 1.340669)’,4326);

Point audi stairs

SELECT ST_GeomFromText(‘POINT(103.9629548 1.34095539)’,4326);

UPDATE Based on select

UPDATE gis.squares_l1_05m SET project_no = 1 FROM(SELECT gis.squares_l1_05m.geom – filter hexagons contained by ccl1 FROM gis.ccl1 LEFT JOIN gis.squares_l1_05m ON ST_Contains(gis.ccl1.geom, gis.squares_l1_05m.geom) AND (ST_Intersects( ST_MPolyFromText(‘MULTIPOLYGON((( 103.963066660790901 1.340752667639772, 103.963069254941715 1.34075718931442 , 103.963074442767294 1.340757189037779, 103.96307703644203 1.340752667086492, 103.963074442291202 1.340748145411852, 103.963069254465651 1.340748145688492, 103.963066660790901 1.340752667639772 )))’, 4326), gis.squares_l1_05m.geom))) as foo

For update

SELECT * from gis.squares_l1_05m WHERE (gis.squares_l1_05m.a, gis.squares_l1_05m.b) IN (VALUES (1,1), (1,2), (1,3))

SELECT foo.project_no, ST_UNION(foo.geom) FROM (SELECT gis.squares_l1_05m.* as foo – filter hexagons contained by ccl1 FROM gis.ccl1 LEFT JOIN gis.squares_l1_05m ON ST_Contains(gis.ccl1.geom, gis.squares_l1_05m.geom) – filter hexagons out hexagon that intersects ccl1_pits LEFT JOIN gis.ccl1_pits ON ST_Intersects(gis.ccl1_pits.geom, gis.squares_l1_05m.geom) WHERE gis.ccl1_pits.gid IS NULL AND gis.squares_l1_05m.project_no != -1) foo

GROUP BY foo.project_no