ninjalikeme/postgis_migration

By ninjalikeme

•Updated almost 9 years ago

Postgis cookbook migration

Image

253

Overview Tags

ninjalikeme/postgis_migration repository overview

⁠Chapter 7 Migration

⁠Things to do before.

⁠Install docker

https://www.docker.com/⁠

⁠Sections

⁠1. Importing LIDAR data

Generate pipeline json files from .las using python script.

$ python sections/1_import/insert_files.py -f <lasfiles_path>

in the data folder run

$ cd <DATA_FOLDER>
$ for file in `ls pipelines/*.json`;do pdal pipeline $file; done

⁠Merge all tables into a single one.

DROP TABLE IF EXISTS chp07.lidar;
CREATE TABLE chp07.lidar AS WITH patches AS 
(
   SELECT
      pa 
   FROM
      "chp07"."N2210595" 
   UNION ALL
   SELECT
      pa 
   FROM
      "chp07"."N2215595" 
   UNION ALL
   SELECT
      pa 
   FROM
      "chp07"."N2220595"
)
SELECT
   2 AS id,
   PC_Union(pa) AS pa 
FROM
   patches;

⁠Spatialize data.

CREATE TABLE chp07.lidar_patches AS WITH pts AS 
(
   SELECT
      PC_Explode(pa) AS pt 
   FROM
      chp07.lidar
)
SELECT
   pt::geometry AS the_geom 
FROM
   pts;
ALTER TABLE chp07.lidar_patches ADD COLUMN gid serial;
ALTER TABLE chp07.lidar_patches ADD PRIMARY KEY (gid);

⁠2. Performing 3D queries on a lidar point cloud

⁠Create 2D index.

CREATE INDEX chp07_lidar_the_geom_idx 
ON chp07.lidar USING gist(the_geom);

CREATE INDEX chp07_lidar_the_geom_3dx
ON chp07.lidar USING gist(the_geom gist_geometry_ops_nd);

Store hydro layer into postgis

$ shp2pgsql -s 3734 -d -i -I -W LATIN1 -t 3DZ -g the_geom hydro_line chp07.hydro | PGPASSWORD=me psql -U me -d "postgis-cookbook" -h localhost

⁠Simple query

DROP TABLE IF EXISTS chp07.lidar_patches_within;
CREATE TABLE chp07.lidar_patches_within AS
SELECT
   chp07.lidar_patches.gid, chp07.lidar_patches.the_geom
FROM
   chp07.lidar_patches,
   chp07.hydro 
WHERE
   ST_3DDWithin(chp07.hydro.the_geom, chp07.lidar_patches.the_geom, 5);

Second option with distinct

DROP TABLE IF EXISTS chp07.lidar_patches_within_distinct;
CREATE TABLE chp07.lidar_patches_within_distinct AS
SELECT
   DISTINCT (chp07.lidar_patches.the_geom), 
   chp07.lidar_patches.gid 
FROM
   chp07.lidar_patches,
   chp07.hydro 
WHERE
   ST_3DDWithin(chp07.hydro.the_geom, chp07.lidar_patches.the_geom, 5);

⁠3. Constructing and serving buildings 2.5 D

⁠Save functions.

$ psql -U me -d postgis-cookbook -p 8000 -h localhost -f sections/3_servingbuildings/polygon_to_line.sql
$ psql -U me -d postgis-cookbook -p 8000 -h localhost -f sections/3_servingbuildings/threedbuilding.sql

⁠Create Simple building Table.

DROP TABLE IF EXISTS chp07.simple_building;
CREATE TABLE chp07.simple_building AS 
SELECT
   1 AS gid,
   ST_MakePolygon(ST_GeomFromText('LINESTRING(0 0,2 
 0, 2 1, 1 1, 1 2, 0 2, 0 0)')
)
AS the_geom;

⁠Execute query within the simple building table

DROP TABLE IF EXISTS chp07.threed_building;
CREATE TABLE chp07.threed_building AS 
SELECT
   chp07.threeDbuilding(the_geom, 10) AS the_geom 
FROM
   chp07.simple_building;

⁠Load building_footprints shapefile.

shp2pgsql -s 3734 -d -i -I -W LATIN1 -g the_geom building_footprints chp07.building_footprints | psql -U me -d postgis-cookbook -h <HOST> -p <PORT>

⁠Perform sql query with shapefile.

DROP TABLE IF EXISTS chp07.build_footprints_threed;
CREATE TABLE chp07.build_footprints_threed AS 
SELECT
   gid,
   height,
   chp07.threeDbuilding(the_geom, height) AS the_geom 
FROM
   chp07.building_footprints;

⁠4. Using ST_Extrude to extrude building footprints

⁠Perform Extruded query.

DROP TABLE IF EXISTS chp07.buildings_extruded;
CREATE TABLE chp07.buildings_extruded AS 
SELECT
   gid,
   ST_CollectionExtract(ST_Extrude(the_geom, 20, 20, 40), 3) as the_geom
FROM
   chp07.building_footprints

⁠5. Creating arbitrary 3D objects for PostGIS

⁠Store .ply file in the database

Using this json into giraffe.json

{
  "pipeline": [{
    "type": "readers.ply",
    "filename": "/data/giraffe/giraffe.ply"
  }, {
    "type": "writers.pgpointcloud",
    "connection": "host='localhost' dbname='postgis-cookbook' user='me' password='me' port='5432'",
    "table": "giraffe",
    "srid": "3734",
    "schema": "chp07"
  }]
}

Execute

$ pdal pipeline giraffe.json"

⁠Exporting models as X3D for the Web

⁠Generate html template

Note. Sql file stored in sections/5_arbitrary3d/5_select_x3d_2.sql

COPY(WITH pts AS (SELECT PC_Explode(pa) AS pt FROM chp07.giraffe)
  SELECT regexp_replace('
  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
  <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
      <meta http-equiv="X-UA-Compatible" content="chrome=1" />
      <meta http-equiv="Content-Type" content="text/html;charset=utf-8" 
        />
      <title>Point Cloud in a Browser</title>
      <link rel="stylesheet" type="text/css" 
          href="http://x3dom.org/x3dom/example/x3dom.css" />
      <script type="text/javascript" 
        src="http://x3dom.org/x3dom/example/x3dom.js"></script>
    </head>
    <body>
      <h1>Point Cloud in the Browser</h1>
      <p>
        Use mouse to rotate, scroll wheel to zoom, and control (or 
          command) click to pan.
      </p>
      <X3D xmlns="http://www.web3d.org/specifications/x3d-namespace" 
        showStat="false" showLog="false" x="0px" y="0px" width="800px" 
        height="600px">
        <Scene>
          <Transform>
            <Shape>' ||  ST_AsX3D(ST_Union(pt::geometry))  ||
            '</Shape>
          </Transform>
        </Scene>
      </X3D>
    </body>
  </html>', E'[\\n\\r]+','', 'g') FROM pts)
TO STDOUT;

psql -h <HOST> -p <PORT> -U me -d postgis-cookbook -f /code/chapter_7/sql/select_x3d_2.sql > test.html

⁠Creating SQL function.

Note. Sql file stored in sections/5_arbitrary3d/5_select_x3d_function.sql

CREATE OR REPLACE FUNCTION AsX3D_XHTML(geometry)
  RETURNS character varying AS
$BODY$

SELECT regexp_replace('
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="X-UA-Compatible" content="chrome=1" />
    <meta http-equiv="Content-Type" content="text/html;charset=utf-8" 
      />
    <title>Point Cloud in a Browser</title>
    <link rel="stylesheet" type="text/css" 
      href="http://x3dom.org/x3dom/example/x3dom.css" />
    <script type="text/javascript" 
      src="http://x3dom.org/x3dom/example/x3dom.js"></script>
  </head>
  <body>
    <h1>Point Cloud in the Browser</h1>
    <p>
      Use mouse to rotate, scroll wheel to zoom, and control (or 
        command) click to pan.
    </p>
    <X3D xmlns="http://www.web3d.org/specifications/x3d-namespace" 
      showStat="false" showLog="false" x="0px" y="0px" width="800px" 
      height="600px">
      <Scene>
        <Transform>
          <Shape>' ||  ST_AsX3D($1)  ||
          '</Shape>
        </Transform>
      </Scene>
    </X3D>
  </body>
</html>', E'[\\n\\r]+','', 'g') As x3dXHTML;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

⁠Executing function that stores in an html file.

psql -h <HOST> -p <PORT> -U me -d postgis-cookbook -c "copy(WITH pts AS (SELECT PC_Explode(pa) AS pt FROM giraffe) SELECT AsX3D_XHTML(ST_UNION(pt::geometry)) FROM pts) to stdout" > html_file.html

⁠6. Reconstructing Unmanned Aerial Vehicle (UAV) image footprints with PostGIS 3D

⁠Install postgis-etc functions.

$ PGPASSWORD=me psql -U me -d postgis-cookbook -h localhost -p 8000 -f ST_RotateX.sql
$ PGPASSWORD=me psql -U me -d postgis-cookbook -h localhost -p 8000 -f ST_RotateY.sql
$ PGPASSWORD=me psql -U me -d postgis-cookbook -h localhost -p 8000 -f ST_RotateXYZ.sql
$ PGPASSWORD=me psql -U me -d postgis-cookbook -h localhost -p 8000 -f pyramidMaker.sql
$ PGPASSWORD=me psql -U me -d postgis-cookbook -h localhost -p 8000 -f volumetricIntersection.sql

⁠Instal pbr function.

$ PGPASSWORD=me psql -U me -d postgis-cookbook -h localhost -p 8000 -f 6_1_func_tpbr.sql

⁠Load uas shapefile.

shp2pgsql -s 3734 -W LATIN1 uas_locations_altitude_hpr_3734 uas_locations | PGPASSWORD=me psql -U me -d postgis-cookbook -h localhost -p 8000

⁠Perform viewshed query.

DROP TABLE IF EXISTS chp07.viewshed;
CREATE TABLE chp07.viewshed AS
SELECT 1 AS gid, roll, pitch, heading, fileName,
  chp07.pbr(ST_Force3D(geom),
        radians(0)::numeric,
        radians(heading)::numeric,
        radians(roll)::numeric,
        radians(40)::numeric,
        radians(50)::numeric,
        ( (3.2808399 * altitude_a) - 838)::numeric)
  AS the_geom FROM uas_locations;

⁠Restoring lidar_tin backup from file.

PGPASSWORD=me pg_restore -h localhost -p 8000 -U me -d "postgis-cookbook" --schema chp07 --verbose "lidar_tin.backup"

⁠Create smaller version of viewshed table

DROP TABLE IF EXISTS chp07.viewshed;
CREATE TABLE chp07.viewshed AS 
SELECT
   1 AS gid,
   roll,
   pitch,
   heading,
   fileName,
   chp07.pbr(ST_Force3D(geom), radians(0)::numeric, radians(heading)::numeric, radians(roll) ::numeric, radians(40)::numeric, radians(50)::numeric, 1000::numeric) AS the_geom 
FROM
   uas_locations 
WHERE
   fileName = 'IMG_0512.JPG';

⁠Extrude lidar tin

DROP TABLE IF EXISTS chp07.lidar_tin_extruded;
CREATE TABLE chp07.lidar_tin_extruded AS 
SELECT
   ST_Extrude((St_dump(the_geom)).geom, 0, 0, 1), 3)
   AS
   the_geom
FROM
   chp07.lidar_tin;
CREATE INDEX chp07_lidar_tin_extruded_the_geom_3dx
ON 
chp07.lidar_tin_extruded USING gist(the_geom gist_geometry_ops_nd);

⁠Intersect with viewshed

DROP TABLE IF EXISTS chp07.viewshed_true;
CREATE TABLE chp07.viewshed_true AS 
SELECT
   ST_3DIntersection(ST_SetSRID(chp07.viewshed.the_geom, 3734),
             ST_SetSRID(chp07.lidar_tin_extruded.the_geom, 3734)) 
FROM
   chp07.viewshed,
   chp07.lidar_tin_extruded
WHERE
   ST_3DIntersects(ST_SetSRID(chp07.viewshed.the_geom, 3734),
             ST_SetSRID(chp07.lidar_tin_extruded.the_geom, 3734));

⁠7. UAV photogrammetry in PostGIS – point cloud

{
  "pipeline": [{
    "type": "readers.ply",
    "filename": "/data/uas_flight/uas_points.ply"
  }, {
    "type": "writers.pgpointcloud",
    "connection": "host='localhost' dbname='postgis-cookbook' user='me' password='me' port='5432'",
    "table": "uas",
    "schema": "chp07"
  }]
}

pdal pipeline uas_points.json

⁠8. UAV photogrammetry in PostGIS – orthorectification

⁠Creating subset table

DROP TABLE IF EXISTS chp07.uas_subset;
CREATE TABLE chp07.uas_subset AS WITH pts AS 
(
   SELECT
      PC_Explode(pa) AS pt 
   FROM
      chp07.uas
)
SELECT
   pt::geometry AS geom 
FROM
   pts 
ORDER BY
   RANDOM() LIMIT 5000;

⁠Convert the point cloud to voronoi polygons

DROP TABLE IF EXISTS chp07.uas_voronoi;
CREATE TABLE chp07.uas_voronoi AS WITH pts AS 
(
   SELECT
      PC_Explode(pa) AS pt 
   FROM
      chp07.uas 
)
SELECT
(ST_dump(ST_VoronoiPolygons(ST_Collect(pt::geometry)))).geom as geom 
from
   pts;
ALTER TABLE chp07.uas_voronoi ADD COLUMN gid serial NOT NULL PRIMARY KEY;
CREATE INDEX uas_voronoi_the_geom_gist 
ON chp07.uas_voronoi USING gist (the_geom);

⁠Remove unneeded polygons

DROP TABLE IF EXISTS chp07.uas_voronoi_count;
CREATE TABLE chp07.uas_voronoi_count AS WITH DISTINCTION AS 
(
   SELECT DISTINCT
(chp07.uas_voronoi.geom),
      chp07.uas_voronoi.gid,
      count(chp07.uas_subset.*) 
   FROM
      chp07.uas_voronoi 
      LEFT JOIN
         chp07.uas_subset 
         ON ST_Intersects(chp07.uas_subset.geom, chp07.uas_voronoi.geom) 
   group by
      chp07.uas_voronoi.gid,
      chp07.uas_voronoi.geom
)
SELECT
   gid, 
   geom,
   count 
FROM
   DISTINCTION 
WHERE
   count = 1 
   AND ST_Covers(ST_Envelope( (( 
   SELECT
      ST_Union(geom) 
   FROM
      chp07.uas_subset)) ), geom);
CREATE INDEX uas_voronoi_count_the_geom_gist 
ON chp07.uas_voronoi_count USING gist (geom );

⁠Perform table joins and set color.

DROP TABLE IF EXISTS chp07.uas_voronoi_join;
CREATE TABLE chp07.uas_voronoi_join AS 
SELECT
   chp07.uas_voronoi_count.gid,
   chp07.uas_voronoi_count.geom,
   trunc(random() * 255) AS red,
   trunc(random() * 255) AS green,
   trunc(random() * 255) AS blue
FROM
   chp07.uas_voronoi_count
   LEFT JOIN
      chp07.uas_subset
      ON ST_Intersects(uas_voronoi_count.geom, chp07.uas_subset.geom);

⁠Export to raster

WITH tempraster AS (
  SELECT ST_AsRaster(
    ST_Union(geom),
    round(500 * ( (ST_XMax(ST_Collect(geom)) - 
      ST_XMin(ST_Collect(geom))) / (ST_YMax(ST_Collect(geom)) 
      - ST_YMin(ST_Collect(geom))) ))::integer,
  500,
  ARRAY['8BUI', '8BUI', '8BUI'],
  ARRAY[0,0,0],
  ARRAY[0,0,0]) AS rast
  FROM chp07.uas_voronoi_join
  ),
  rasterized AS (
    SELECT ST_Union(ST_AsRaster(
    geom,
    rast,
    ARRAY['8BUI', '8BUI', '8BUI'],
    ARRAY[red, green, blue],
    ARRAY[0,0,0]
    )
  ) AS raster
  FROM
  (
    SELECT * FROM
    chp07.uas_voronoi_join
    CROSS JOIN
    tempraster
  ) AS pcr
)
SELECT write_file(ST_AsPNG(raster), '/data/test.png'::text) FROM 
  rasterized;

⁠9. UAV photogrammetry in PostGIS – DSM creation

DROP TABLE IF EXISTS chp07.uas_tin;
CREATE TABLE chp07.uas_tin AS WITH pts AS 
(
   SELECT
      PC_Explode(pa) AS pt 
   FROM
      chp07.uas_flights
)
SELECT
   ST_DelaunayTriangles(ST_Union(pt::geometry), 0.0, 2) AS the_geom 
FROM
   pts;

Tag summary

Recent tags

Content type

Image

Digest

Size

685.8 MB

Last updated

almost 9 years ago

docker pull ninjalikeme/postgis_migration