Here is a sample script in using buffer in Postgis. I buffered the line by 10 and 20 meters. Take note that I have to transform the geometry to the corresponding EPSG, so I could specify “meters”.
DROP TABLE busline_buffer1;
DELETE FROM geometry_columns WHERE f_table_name = 'busline_buffer1';
CREATE TABLE busline_buffer1( gid serial, CONSTRAINT pk_buffer1 PRIMARY KEY(gid));
SELECT AddGeometryColumn('public', 'busline_buffer1', 'the_geom', 4326, 'POLYGON',2);
// This would display what the output of the geometry IS...
SELECT AsText( transform( ST_BUFFER( transform(v1.the_geom, 32650), 10 ), 4326 ) ) FROM buslinev1 v1;
INSERT INTO busline_buffer1(the_geom) SELECT transform( ST_BUFFER( transform(v1.the_geom, 32650), 10 ), 4326 ) FROM buslinev1 v1;
SELECT gid, AsText(the_geom) FROM busline_buffer1;
========================================
DROP TABLE busline_buffer2;
DELETE FROM geometry_columns WHERE f_table_name = 'busline_buffer2';
CREATE TABLE busline_buffer2( gid serial, CONSTRAINT pk_buffer2 PRIMARY KEY(gid));
SELECT AddGeometryColumn('public', 'busline_buffer2', 'the_geom', 4326, 'POLYGON',2);
SELECT AsText( transform( ST_BUFFER( transform(v1.the_geom, 32650), 20 ), 4326 ) ) FROM buslinev1 v1;
INSERT INTO busline_buffer2(the_geom) SELECT transform( ST_BUFFER( transform(v1.the_geom, 32650), 20 ), 4326 ) FROM buslinev1 v1;
SELECT gid, AsText(the_geom) FROM busline_buffer2;
This is a quick-command list of Postres. If you want detailed instructions, please visit the Postgres Manual.
1. How do I Show all databases?
postgres=# \l
List of databases
Name | Owner | Encoding
------------------+----------+----------
postgis | postgres | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
template_postgis | postgres | UTF8
(5 rows)
Note: Do not drop template databases if not necessary.
2. How do I run a script from the prompt?
\i
OR
psql -d cybersoftbj -u user < myfile.sql
Its very usuful in reloading user-defined functions.
3. How do I create a user?
CREATE ROLE lbs WITH LOGIN PASSWORD 'tracking' SUPERUSER INHERIT CREATEDB CREATEROLE;
4. How to dump database in a text file?
pg_dump -U lbs -d cybersoftbjv1 -h 127.0.0.1 -W > cybersoftbjv1.sql
4. How to dump database cleanly?
pg_dump -c -d -E UTF8 -h 127.0.0.1 -U lbs -W platform_v1 > platform_v1.sql
5. How to update using two tables?
UPDATE road_for_update u
SET the_geom = r.the_geom
FROM roads r
WHERE r.rd_id = u.rd_id;
6. How to change a column type with Cast?
ALTER TABLE roads ALTER COLUMN class_new TYPE integer USING class_new::integer;
7. How to provide/restrict access privileges to tables?
GRANT SELECT ON TABLE table TO user;
REVOKE SELECT ON TABLE table FROM user;
8. How to add a geometry column to a table?
SYNTAX: AddGeometryColumn(
,
, , , )
EXAMPLE: SELECT AddGeometryColumn(‘public’, ‘poi’, ‘the_geom’, 4326, ‘POINT’, 2)
9. Changing column names with spaces?
ALTER TABLE class_aroundme RENAME "level 1" TO level_1; 10. Setting kernel shmmax for postgres
sysctl -w kernel.shmmax=134217728
Note: For permanent changes see /etc/sysctl.cfg
11. How to backup table(s) from pg_dump?
pg_dump poi_beijing -t class -t poi_class -f $BACKUPDIR/test_$MYDATE.sql
1. It is important that you already have postgres, postgis, pgRouting installed in your machine.
A. The schema. Below is the sample schema that is a derivative of the kanagawa sample data from pgRouting. Take note of the source and target nodes, as well as the length and the node coordinates (x1,y1; x2,y2) of the line.
cybersoftbj=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | geometry_columns | TABLE | postgres
public | roads | TABLE | postgres
public | spatial_ref_sys | TABLE | postgres
(3 rows)
cybersoftbj=# \d roads
TABLE "public.roads"
COLUMN | Type | Modifiers
------------+------------------------+-----------------------------------------------------
gid | integer | NOT NULL DEFAULT NEXTVAL('roads_gid_seq'::regclass)
rd_id | bigint
yutu_id | integer
block_id | bigint
heirarchy | character varying(5)
cn_name | character varying(75)
py_name | character varying(100)
source | bigint
target | bigint
x1 | numeric
y1 | numeric
x2 | numeric
y2 | numeric
costlength | numeric
the_geom | geometryA. Extracting the coordinates of the line segments from Mapinfo.
1. I have to format the data structure as follows…
…here is the roads table after weeding out some unnecessary columns…
… adding the source,target,x1,y1,x2,y2,costlength…
2. Using ObjectGeography. Download the MapBasic Reference
ObjectGeography( object, attribute )
ObjectGeography( object, “1″ ) <– gives you the beginning x coord of the point
3. Export the tab file to a shape file for ArchMap.
AFAIK, there are only two ways to load data to PostGIS:
1. Using Insert statements
2. Using Utilities.
Utilities include shp2pgsql which is found in “C:\Program Files\PostgreSQL\8.2\bin\” or “/usr/local/pgsql/bin”. To load Mapinfo table, I have used the OGR utilities from FWTools for Windows. and used gdal1.3.2 (since it contains ogr) for Unix.
OGR2OGR CheatSheet should be a good kickstart for basic understanding. For the impatient..
ogr2ogr -f "PostGreSQL" -nlt LINESTRING -a_srs "EPSG:4326" PG:"host=localhost user=username password=mypassword dbname=mydb mytab.TAB -select columnName
or
ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" mytabfile.tab -nln newtablename -select columnName
Bear in mind that you should select out the columns from your mapfile (mine is chinese) especially if you have a diffent encoding in your column which matches your database (postgres). You might get a “Terminating translation prematurely after failed translation of layer [layername]” error. Since Mapinfo stores the text to ASCII, my workaround is to export the tabfile to a UTF-8 textfile then upload it to PostGres. Hoping the primary ids would match to make the necessary updates…
An alternative to load Chinese text from Mapinfo to PostGIS is the ff:
1. In Mapinfo use the Universal Translator to export the table into a shape file.
2. Once you have the shape file, you can directly use the shp2pgsql.
shp2pgsql -W “gbk” -s 4326 lbjrdnt_small_polyline roads > roads.sql
Creating the spatial table from scratch…
[postgres@rupert-linux ~]$ su - postgres
[postgres@rupert-linux ~]$ createdb _E UTF-8 template_postgis
CREATE DATABASE
[postgres@rupert-linux ~]$ createlang plpgsql template_postgis
[postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/lwpostgis.sql -d template_postgis
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMIT
postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/spatial_ref_sys.sql -d template_postgis
[postgres@rupert-linux ~]$ psql
Alternatively you could also create your spatial table from a template…
14 postgres=# CREATE DATABASE gistest TEMPLATE=template_postgis;
15 CREATE DATABASEI have also added the routing functions using routing.sql and routing_postgis.sql
[postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/contrib/routing.sql -d template_postgis
[postgres@rupert-linux ~]$ psql -f /usr/local/pgsql/share/contrib/routing_postgis.sql -d template_postgis
FYI…
The spatial_ref_sys table contains the ESPG codes and projections. Currently it has 3162 records.
gistest=# \d spatial_ref_sys;
srid | integer | NOT NULL
auth_name | character varying(256) |
auth_srid | integer |
srtext | character varying(2048) |
proj4text | character varying(2048) |
The geometry_columns contains the geometry type of the table you just created..
gistest=# \d geometry_columns;
f_table_catalog | character varying(256) | NOT NULL
f_table_schema | character varying(256) | NOT NULL
f_table_name | character varying(256) | NOT NULL
f_geometry_column | character varying(256) | NOT NULL
coord_dimension | integer | NOT NULL
srid | integer | NOT NULL
type | character varying(30) | NOT NULL