About
- PostGIS is a free, open source spatial extension for PostgreSQL
- PostGIS adds spatial functions such as distance, area, union, intersection, and specialty geometry data types to PostgreSQL
Installation
- postgres=# CREATE EXTENSION postgis;
- postgres=# CREATE EXTENSION postgis_topology;
List of relations created in Postgres
- geography_columns
- geometry_columns
- raster_columns
- raster_overviews
- spatial_ref_sys
Common data types
- geometry
- geography
- raster
- box2d, box3d
Simple test script
|
#!/bin/bash
db=test-postgis csv=/tmp/test-postgis.csv sql=/tmp/test-postgis.sql
cat >$csv <<END id,name,latitude,longitude 1,Hong Kong,22.396427,114.109497 2,New York,40.712776,-74.005974 3,London,51.507351,-0.127758 END
cat >$sql <<END create table city ( id integer, name text, latitude double precision, longitude double precision ) distributed by (id) ;
copy city from '$csv' header delimiter ',';
alter table city add column geom geometry(POINT,4326);
update city set geom = st_setsrid(st_point(longitude,latitude),4326);
select st_srid(geom) from city;
select * from city; END
createdb $db psql -d $db -f $sql
dropdb $db rm $csv $sql |
References:
- http://workshops.boundlessgeo.com/postgis-intro/
- https://medium.com/@tjukanov/why-should-you-care-about-postgis-a-gentle-introduction-to-spatial-databases-9eccd26bc42b