Posts tagged postgis

Line Buffering in Postgis

Buffering Lines

Jul 4th

Posted by rupert in postgis

2 comments

Line Buffering in Postgis

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;
,

PostgresSQL / PostGIS CheatSheat

Jul 1st

Posted by rupert in postgis

No comments

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.cfg11. How to backup table(s) from pg_dump?
pg_dump poi_beijing -t class -t poi_class -f $BACKUPDIR/test_$MYDATE.sql

,

Preparing routing data for pgRouting

Apr 10th

Posted by rupert in postgis

No comments

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   | geometry

A. 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.

, , , ,

Loading Mapinfo table to PostGis

Apr 5th

Posted by rupert in GDAL/OGR

2 comments

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 a Spatial Table in PostGIS

Apr 5th

Posted by rupert in postgis

2 comments

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 DATABASE

I 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
, ,
«1234»