Skip to content

Latest commit

 

History

History
67 lines (55 loc) · 2.17 KB

Setup.org

File metadata and controls

67 lines (55 loc) · 2.17 KB

QGIS Notes

Data prep

  • Freehand raster georeferencer to align the tiffs to make them geotiffs
    • 1:290 for L1
    • 1:193 for L2
  • Shape tools to make circles/pie wedges
  • mmqgis for hexagon layer
    • create grid layer hexagon, Y spacing 0.1
    • Sort to ensure elements in the right order
  • Open attribute table and added a, b, project_no with field calculator
    • Cause hexagon layer 67 by 75
    • project_no
      • just -1
    • a
      • ceil(@row_number/67)-1
    • b
      • ((@row_number-1)%67)

Data export

  • use export button to 4326
    • cause changing the CRS in any other way will rellocate the coordinates

Postgres setup

– Add postgis extension for even more spatially queries psql -d postgres -c “CREATE EXTENSION postgis;”

– Add Schema for better organisation of tables psql -d postgres -c “CREATE SCHEMA gis;”

– Use shp2pgsql to upload shapefiles to postgres shp2pgsql -s 4326 -I ‘CCL1.shp’ gis.CCL1 | psql -d postgres -U postgres

shp2pgsql -s 4326 -I ‘CCL1_pits.shp’ gis.CCL1_pits | psql -d postgres -U postgres

shp2pgsql -s 4326 -I ‘squares_l1_05m_new1.shp’ gis.squares_l1_05m | psql -d postgres -U postgres

shp2pgsql -s 4326 -I ‘CCL2.shp’ gis.CCL2 | psql -d postgres -U postgres

shp2pgsql -s 4326 -I ‘CCL2_pits.shp’ gis.CCL2_pits | psql -d postgres -U postgres

psql -d postgres -c ” CREATE TABLE gis.squares1 as SELECT squares_l1_05m.gid, squares_l1_05m.project_no, squares_l1_05m.a, squares_l1_05m.b, squares_l1_05m.geom FROM gis.ccl1 LEFT JOIN gis.squares_l1_05m ON st_contains(ccl1.geom, squares_l1_05m.geom) LEFT JOIN gis.ccl1_pits ON st_intersects(ccl1_pits.geom, squares_l1_05m.geom) WHERE ccl1_pits.gid IS NULL;”

psql -d postgres -c ” CREATE TABLE gis.squares2 as SELECT squares_l1_05m.gid, squares_l1_05m.project_no, squares_l1_05m.a, squares_l1_05m.b, squares_l1_05m.geom FROM gis.ccl2 LEFT JOIN gis.squares_l1_05m ON st_contains(ccl2.geom, squares_l1_05m.geom) LEFT JOIN gis.ccl2_pits ON st_intersects(ccl2_pits.geom, squares_l1_05m.geom) WHERE ccl2_pits.gid IS NULL;”

ALTER TABLE gis.squares1 ADD PRIMARY KEY (gid); ALTER TABLE gis.squares2 ADD PRIMARY KEY (gid);