[technest] GIS Notes from last night
Daniel Sobey
dns at dns.id.au
Thu Jun 26 22:34:38 EST 2014
Firstly install postgresql 9.3, you can use earlier versions but you need
9.3 for json support.
I am using ubuntu so these instructions will work with ubuntu/debian, other
distributions may be slightly different.
Login as postgres as we need an admin account.
Create a user for this database and set this as the owner.
$ sudo su - postgres
$ createuser username;
$ createdb -O username playgrounddb
Now we need to set a password for the account and install the extensions
$ psql playground
psql (9.3.4)
Type "help" for help.
postgres=# alter user username with password 'Abcd1234';
postgres=# CREATE EXTENSION postgis;
postgres=# CREATE EXTENSION postgis_topology;
postgres=# CREATE TYPE JSON;
Now we can create and import tables:
-- Suburbs --
This is a shape file that can be found on data.sa.gov.au
http://www.dptiapps.com.au/dataportal/Suburbs.zip
unzip Suburbs.zip
shp2pgsql -s 4326 -e Suburbs.shp > Suburbs.sql
psql playground < Suburbs.sql
Column | Type | Modifiers
------------+-----------------------------+-------------------------------------------------------
gid | integer | not null default
nextval('suburbs_gid_seq'::regclass)
objectid | numeric(10,0) |
postcode | character varying(6) |
suburb | character varying(40) |
suburb_num | numeric(10,0) |
shape_area | numeric |
shape_len | numeric |
geom | geometry(MultiPolygon,4326) |
Indexes:
"suburbs_pkey" PRIMARY KEY, btree (gid)
-- Roads --
http://www.dptiapps.com.au/dataportal/Roads.zip
unzip Roads.zip
shp2pgsql -s 4326 -e Roads.shp > Roads.sql
psql playground < Road.sql
Column | Type |
Modifiers
------------+--------------------------------+-----------------------------------------------------
gid | integer | not null default
nextval('roads_gid_seq'::regclass)
objectid | numeric(10,0) |
persistent | numeric(10,0) |
featurecod | numeric(10,0) |
name | character varying(50) |
roadtype | character varying(15) |
width | numeric(10,0) |
class | character varying(4) |
surface | character varying(4) |
lanes | character varying(4) |
routenum | character varying(12) |
status | character varying(4) |
horizontal | numeric |
minscale | numeric(10,0) |
maxscale | numeric(10,0) |
shape_len | numeric |
geom | geometry(MultiLineString,4326) |
Indexes:
"roads_pkey" PRIMARY KEY, btree (gid)
-- earthquakes --
This is a csv file that we can load into the database.
When using the postgres copy command you need admin permission so the easy
way to do this is sudo su - postgres and run the command there.
This involves 2 steps, creating a staging table and then creating a table,
view or materialized view
When importing a table using the copy command of postgres it can create a
table with each column as text as below
Table "public.earthquake_staging"
Column | Type | Modifiers
-----------+------+-----------
dated | text |
time | text |
seconds | text |
longitude | text |
latitude | text |
depth | text |
place | text |
magnitude | text |
intensity | text |
accuracy | text |
arrivals | text |
residual | text |
stations | text |
This should be converted to a proper table with correct data types.
The dated is a string '31/03/1840' time is a string '1630' seconds is a
string '0' this can be concatenated to form a date '31/03/1840 16:30:0'
As 1630 is 16 hours and 30 minutes use floor and / to get the hour and %
(mod) to get the minutes.
The other thing needed is to convert the longitude and latitude columns
into a point data type.
This is done by using the GeomFromEWKT() function with a string 'SRID=4326;
POINT( 138.813 -32.531)' as the parameter.
http://data.sa.gov.au/storage/f/2013-05-21T05%3A51%3A01.742Z/dmitre-earthquake-new.csv
copy earthquake_staging from
'/var/lib/postgresql/dmitre-earthquake-new.csv' CSV header;
create view earthquake as select ROW_NUMBER() OVER () as id, cast(dated||'
'||floor(cast(time as integer)/100)||':'||cast(time as integer)%100 as
timestamp) dated, GeomFromEWKT('SRID=4326; POINT(' ||longitude ||' '||
LATITUDE||')') as geom,cast(depth as real),place,cast(magnitude as
real),cast(intensity as integer),accuracy,cast(arrivals as
integer),cast(residual as integer), cast(stations as integer) from
earthquake_staging ;
create table earthquake as select ROW_NUMBER() OVER () as id,
cast(dated||' '||floor(cast(time as integer)/100)||':'||cast(time as
integer)%100||seconds as timestamp) dated, GeomFromEWKT('SRID=4326; POINT('
||longitude ||' '|| LATITUDE||')') as geom,cast(depth as
real),place,cast(magnitude as real),cast(intensity as
integer),accuracy,cast(arrivals as integer),cast(residual as integer),
cast(stations as integer) from earthquake_staging ;
The resulting view is:
Column | Type | Modifiers
-----------+-----------------------------+-----------
id | bigint |
dated | timestamp without time zone |
geom | geometry |
depth | real |
place | text |
magnitude | real |
intensity | integer |
accuracy | text |
arrivals | integer |
residual | integer |
stations | integer |
-- Playgrounds --
Copy this to a staging table and then create a view:
http://data.sa.gov.au/storage/f/2013-05-24T02%3A36%3A40.663Z/playgrounds-for-datagovau.txt
create table playground_staging ( XCoord text, YCoord text, Name text,
Location text, Council text);
copy playground_staging from
'/var/lib/postgresql/playgrounds-for-datagovau.txt' DELIMITER ';' CSV
header;
create view playground as select ROW_NUMBER() OVER () as id,
GeomFromEWKT('SRID=4326; POINT('||XCoord ||' '|| YCoord||')') as geom,
name,location,council from playground_staging;
Column | Type | Modifiers
----------+----------+-----------
id | bigint |
geom | geometry |
name | text |
location | text |
council | text |
Queries:
When you query the geometry column you will notice that the geometry column
is in binary.
To make this human readable use ST_AsText() to convert this to a string.
playground=# select * from earthquake limit 1;
id | dated | geom
| depth | place | magnitude | intensity | accuracy | arrivals
| residual | stations
----+---------------------+----------------------------------------------------+-------+-----------------+-----------+-----------+----------+----------+----------+----------
1 | 1840-03-31 06:00:00 |
0101000020E6100000333333333353614033333333337341C0 | 7 | ADELAIDE
| 2.7 | | | 0 | 0 | 0
playground=# select id,dated,
st_astext(geom),depth,place,magnitude,intensity,accuracy,arrivals,residual,stations
from earthquake limit 1;
id | dated | st_astext | depth | place
| magnitude | intensity | accuracy | arrivals | residual | stations
----+---------------------+-----------------------+-------+-----------------+-----------+-----------+----------+----------+----------+----------
1 | 1840-03-31 06:00:00 | POINT(138.6 -34.9) | 7 | ADELAIDE
| 2.7 | | | 0 | 0 | 0
Use the ST_Within(geom,geom) to compare if a point is within another point.
playground=# select suburb,count(*) from suburbs, earthquake where
ST_Within(earthquake.geom, suburbs.geom) group by suburb order by 2 desc
limit 5;
suburb | count
-----------------+-------
FLINDERS RANGES | 720
MELROSE | 378
BOOLEROO CENTRE | 223
MANNANARIE | 181
HAWKER | 170
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.linux.org.au/pipermail/technest/attachments/20140626/40b72dac/attachment.htm
-------------- next part --------------
A non-text attachment was scrubbed...
Name: gis slides.pdf
Type: application/pdf
Size: 77819 bytes
Desc: not available
Url : http://lists.linux.org.au/pipermail/technest/attachments/20140626/40b72dac/attachment.pdf
More information about the technest
mailing list