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