Melbourne Data Report

Basic postSQL Code

Geometry Object Data Type

1. point
2. LineString
3. Polygon

Distance Comparison → st_distance(geometry g1, geometry g2)

Check if gemoetries are equal → st_equals(geometry A, geometry B)

Test whether the interiors of the geometries intersect → st_intersects(geometry A, geometry B)

Opposite of intersect → st_disjoint(geometry A, geometry B)

Intersection results in a geometry whose dimension is one less than the maximum dimension of the two source → st_crosses (geometry A, geometry B)

Two geoemtries has at least one point not shared by other → st_overlaps(geometry A, geometry B)

Two geometries only touches their boundary → st_touches(geometry A, geometry B)

Fully within the other (but first geometry inside second geometry) → st_within(geometry A, geometry B)

Fully within the other (but second geometry insdie first geometry) → st_contains(geometry A, geometry B)

st_within

st_contains

Expand the side of a spatial object within certain radius → st_buffer(geometry g1, float rad, int e)

💡 if you want to use a real life measurment ex) 500m

st_buffer(geometry g1 :: geography , 500) 

Obtain the boundary line of a polygon → st_boundary (Geometry geom A)

Obtain the centroid of a polygon → st_centroid (geometry g1)

Create a convex hull from a set of spatial objects → st_convexhull (geometry geom A)
Returns the area of a polygonal geometry → st_area(geometry g1)

1. How to insert a geometrical data in to dbeaver

1. Run

docker exec -it postgis-db-1 bash 

2. insert the file into dbeaver schema using the command below

ogr2ogr PG:"dbname = dbname user = username " "datafile.shp" -nln schemaname.tablename -overwrite

ex) ogr2ogr PG:"dbname=gisdb user=postgres" “gis_osm_landuse_a_free_1.shp” -nln south_korea.landuse -overwrite

💡 if your data has multipolygons it will show an error like this

In this case change a command to this

ogr2ogr PG:"dbname = dbname user = username " "datafile.shp" -nlt multipolygon -nln schemaname.tablename -overwrite

2. How to insert a texfile data separated with ‘,’ in to dbeaver

create table melbourne.trips2
(route_id			char(100),
service_id			char(100),
trip_id				char(100),
shape_id     		char(100),
trip_headsign		char(100),
direction_id		char(100));

copy melbourne.trips2(route_id,service_id,trip_id,shape_id,trip_headsign,direction_id)
from '/home/gtfs/2/google_transit/trips.txt'
delimiter ','
csv header;

3. Count the bus of each bus stops of great melbourne

my laptop specs - macbook pro 14 2021

chip : Apple M1 Pro

memory: 32GB

3-1. count all the meshblocks of greator melbourne

create table melbourne.melb_mb as
select *
from melbourne.meshblock_au ma 
where gcc_name21 like '%Melb%';
select count(*) from melbourne.melb_mb mm ;

3-2 . Create buffer of 500m wide of each meshblocks - 1m 17s

3-3 Creat table only including the bus stops of the greater melbourne - 96ms

create table melbourne.stops_melb4 as
select distinct s.stop_id, s.geom
from melbourne.stops4 s
join melbourne.melb_buff m 
on st_contains(m.geom_buf,s.geom)

3-4 By the relationship below we can get the routes of the stops - 36m 3s

💡 How join works in SQL

create table melbourne.routes_stops as
select r.route_short_name, r.route_long_name,
       r.route_type, t.trip_id, s.stop_id
from melbourne.routes4 r 
join melbourne.trips4 t 
on (r.route_id=t.route_id) 
join melbourne.stop_times4 st 
on (t.trip_id = st.trip_id) 
join melbourne.stops_melb s
on (s.stop_id=st.stop_id);

3-5 get the distinct bust routes of each stops - 52m 48s

create table melbourne.stops_num as
select stop_id, count(*) num
from melbourne.stops_routes
group by stop_id;

3-6 . count how many routes for each stop - 1.360s

reate table melbourne.stops_num as
select stop_id, count(*) num
from melbourne.stops_routes
group by stop_id;

3-7 Create buffer for residential meshblocks -2m 13s

drop table melbourne.melb_mb_buff ;
create table melbourne.melb_mb_buff as 
select *,
       st_buffer(mm.wkb_geometry::geography,500)::geometry geom_buff 
from melbourne.melb_mb mm
where mb_cat21 ='Residential';

3-8 Get the stops of residential meshblocks - 1m 42s

drop table melbourne.mb_stops ;
create table melbourne.mb_stops as
select m.mb_code21 , s.stop_id 
from melbourne.melb_mb_buff m
join melbourne.stops_melb s
on st_contains(m.geom_buff,s.geom);

create index on melbourne.stops_melb using gist(geom);

3-9 Get routes in each stop

create table melbourne.mb_routes as
select m.mb_code21, s.route_short_name  
from melbourne.mb_stops m 
join melbourne.stops_routes s 
on (m.stop_id=s.stop_id);

3-10 Aggreate and join with the geometry

create table melbourne.mb_routes_num as
select mb.mb_code21 , mb.wkb_geometry , count(mr.route_short_name) num 
from melbourne.mb_routes mr 
join melbourne.melb_mb_buff mb 
on (mr.mb_code21=mb.mb_code21)
group by mb.mb_code21 , mb.wkb_geometry

4. Aggreate in sa3 format ( mb_routes with sa3 shape meshblocks)

• melbourne divided by sa3 code

4-1 Create table by sa3 meshblocks

create table melbourne.melb_sa3 as
select sa3_code21,st_union(wkb_geometry) geom
from melbourne.melb_mb mm
group by sa3_code21

4-2 get the distinct route of each sa3 meshblocks - 13m 7s

create table melbourne.sa3_routes_num_new as 
select distinct mm.sa3_code21,s.geom, count(distinct mr.route_short_name) num 
from melbourne.mb_routes mr
join melbourne.melb_mb mm 
on (mr.mb_code21 = mm.mb_code21)
join melbourne.melb_sa3 s 
on (mm.sa3_code21 = s.sa3_code21)
group by mm.sa3_code21, s.geom;

4-2 ver2 separating geometry col with aggreagation - 38s more than 20times faster than ver1

create table melbourne.sa3_routes_num_new2 as
with sa3_bus as
(select mm.sa3_code21, count(distinct mr.route_short_name) num
from melbourne.mb_routes mr
join melbourne.melb_mb mm 
on (mr.mb_code21 = mm.mb_code21)
group by mm.sa3_code21)
select b.sa3_code21, s.geom, b.num
from sa3_bus b
join melbourne.melb_sa3 s 
on (b.sa3_code21=s.sa3_code21);