Ngga bagus untuk emi…

Di PG Admin

CREATE DATABASE mapserver TEMPLATE=template_postgis

Download ini dulu

http://test.gdal.org/fwtools/FWTools130.exe

Rubah dari mapinfo ke Postgres

C:\Program Files\FWTools1.3.0\bin>ogr2ogr -f PostgreSQL PG:”host=localhost user=postgres dbname=mapserver password=postgres” jakarta_jalan.tab

Rubah dari mapinfo ke MySQL

C:\Program Files\FWTools1.3.0>ogr2ogr -append -f MySQL MySQL:test,user=root,host
=localhost c:\tmp\trayek.tab
ERROR 1: MySQL error message:The used table type doesn’t support SPATIAL indexes
Description: ALTER TABLE trayek ADD SPATIAL INDEX(SHAPE)
ERROR 1: Terminating translation prematurely after failed
translation of layer trayek

C:\Program Files\FWTools1.3.0>ogr2ogr -append -f MySQL MySQL:test,user=root,host
=localhost c:\tmp\trayek.tab

C:\Program Files\FWTools1.3.0>ogrinfo MySQL:test,user=root trayek -so
INFO: Open of `MySQL:test,user=root’
using driver `MySQL’ successful.

Layer name: trayek
Geometry: Line String
Feature Count: 181
Extent: (106.581117, -6.486284) – (107.153991, -6.095809)
Layer SRS WKT:
GEOGCS[“unnamed”,
DATUM[“WGS_1984”,
SPHEROID[“WGS 84”,6378137,298.257223563],
TOWGS84[0,0,0,0,0,0,0]],
PRIMEM[“Greenwich”,0],
UNIT[“degree”,0.0174532925199433]]
FID Column = OGR_FID
Geometry Column = SHAPE

Rubah dari mapinfo ke ESRI Shapefile

C:\ms4w\Apache\cgi-bin>ogr2ogr -f “ESRI Shapefile” jakarta_jalan.shp jakarta_jalan.tab

Mo tau config layernya?

LAYER
NAME jalan_postgre
METADATA
“DESCRIPTION” “Jalan arteri”
END
TYPE LINE
STATUS OFF
CONNECTIONTYPE postgis
CONNECTION “host=localhost dbname=mapserver user=postgres password=postgres”
DATA “wkb_geometry from jakarta_jalan”
CLASS
NAME “NAMA JALAN”
STYLE
COLOR 255 0 0
MAXSIZE 10 # default 50
SYMBOL ‘solidLine’
SIZE 5
END #style
LABEL
ANGLE 0
COLOR 255 0 0
FONT pixel
TYPE truetype
SIZE 8
POSITION AUTO
PARTIALS FALSE
BUFFER 30
OUTLINECOLOR 255 255 255
END
COLOR 255 0 0
OUTLINECOLOR 0 255 0
END
TOLERANCE 5
END # Layer

Ini bagus juga buat bikin ide

What is the total length of all roads, expressed in kilometers?

You can answer this question with a very simple piece of SQL:

postgis=# SELECT sum(length(the_geom))/1000 AS km_roads FROM bc_roads;
km_roads
——————
70842.1243039643
(1 row)

How large is the city of Prince George, in hectares?

This query combines an attribute condition (on the municipality name) with a spatial calculation (of the area):

postgis=# SELECT area(the_geom)/10000 AS hectares FROM bc_municipality
WHERE name = ‘PRINCE GEORGE’;
hectares
——————
32657.9103824927
(1 row)

What is the largest municipality in the province, by area?

This query brings a spatial measurement into the query condition. There are several ways of approaching this problem, but the most efficient is below:

postgis=# SELECT name, area(the_geom)/10000 AS hectares
FROM bc_municipality
ORDER BY hectares DESC
LIMIT 1;
name | hectares
—————+—————–
TUMBLER RIDGE | 155020.02556131
(1 row)

Note that in order to answer this query we have to calculate the area of every polygon. If we were doing this a lot it would make sense to add an area column to the table that we could separately index for performance. By ordering the results in a descending direction, and them using the PostgreSQL “LIMIT” command we can easily pick off the largest value without using an aggregate function like max().
What is the length of roads fully contained within each municipality?

This is an example of a “spatial join”, because we are bringing together data from two tables (doing a join) but using a spatial interaction condition (“contained”) as the join condition rather than the usual relational approach of joining on a common key:

postgis=# SELECT m.name, sum(length(r.the_geom))/1000 as roads_km
FROM bc_roads AS r,bc_municipality AS m
WHERE r.the_geom && m.the_geom
AND contains(m.the_geom,r.the_geom)
GROUP BY m.name
ORDER BY roads_km;

name | roads_km
—————————-+——————
SURREY | 1539.47553551242
VANCOUVER | 1450.33093486576
LANGLEY DISTRICT | 833.793392535662
BURNABY | 773.769091404338
PRINCE GEORGE | 694.37554369147
…

This query takes a while, because every road in the table is summarized into the final result (about 250K roads for our particular example table). For smaller overlays (several thousand records on several hundred) the response can be very fast.

Create a new table with all the roads within the city of Prince George.

This is an example of an “overlay”, which takes in two tables and outputs a new table that consists of spatially clipped or cut resultants. Unlike the “spatial join” demonstrated above, this query actually creates new geometries. An overlay is like a turbo-charged spatial join, and is useful for more exact analysis work:

postgis=# CREATE TABLE pg_roads as
SELECT intersection(r.the_geom, m.the_geom) AS intersection_geom,
length(r.the_geom) AS rd_orig_length,
r.*
FROM bc_roads AS r, bc_municipality AS m
WHERE r.the_geom && m.the_geom
AND intersects(r.the_geom, m.the_geom)
AND m.name = ‘PRINCE GEORGE’;

What is the length in kilometers of “Douglas St” in Victoria?

postgis=# SELECT sum(length(r.the_geom))/1000 AS kilometers
FROM bc_roads r, bc_municipality m
WHERE r.the_geom && m.the_geom
AND r.name = ‘Douglas St’
AND m.name = ‘VICTORIA’;
kilometers
——————
4.89151904172838
(1 row)

What is the largest municipality polygon that has a hole?

postgis=# SELECT gid, name, area(the_geom) AS area
FROM bc_municipality
WHERE nrings(the_geom) > 1
ORDER BY area DESC LIMIT 1;
gid | name | area
—–+————–+——————
12 | SPALLUMCHEEN | 257374619.430216
(1 row)