OVERVIEW OF INSTALLATION PostgreSQL and PostGIS
MAINTAINING CONSISTENCY IN COLUMN SPATIAL DATA Make sure we meet the geometry column OpenGIS standard specification. One of them with the function IsValid (). For example: select IsValid ('LINESTRING (0 0, 1 1)'), IsValid ('LINESTRING (0 0.0 0)'); IsValid | IsValid ---------+--------- t | f There are ways to make us keep the data in order to remain compliant, ie by creating a check constraint. For example: ALTER TABLE mytable ADD CONSTRAINT CHECK geometry_valid_check (IsValid (the_geom)); check constraint will also be created automatically if you add a column geometric with AddGeometryColumn command (). LOADING spatial data There are several methods we can use. The first way is to use insert query command as usual. Suppose we create a file (roads.sql) containing: www.hatma.info 2 WebgGIS with MapServer - MapLab - Chameleon - PostGIS BEGIN; INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES (1, GeomFromText ('LINESTRING (243,118.191108 191 232 243 242)', -1), 'Jeff Rd'); INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES (2, GeomFromText ('LINESTRING (244,158.189265 189 141 244 817)', -1), 'Geordie Rd'); INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES (3, GeomFromText ('LINESTRING (228,138.192612 192 783 229 814)', -1), 'St. Paul'); INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES (4, GeomFromText ('LINESTRING (252,431.189631 189 412 259 122)', -1), 'Graeme Ave'); INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES- (5, GeomFromText ('LINESTRING (224,148.190871 190 131 228 134)', -1), 'Phil TCE'); INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES- (6, GeomFromText ('LINESTRING (263,418.198213 198 231 268 322)', -1), 'Dave Cres'); COMMIT; Then execute the command on the "psql" SQL terminal monitor: psql-d [database]-f roads.sql USING SHP2PGSQL If we already have a shp file that contains our spatial data, easier using data shp2pgsql loader that will convert ESRI shp file into SQL. Shp2pgsql can we run on the "psql" SQL terminal monitor. For example: shp2pgsql shaperoads myschema.roadstable> roads.sql psql-d-f roadsdb roads.sql Or: shp2pgsql shaperoads myschema.roadstable | psql-d roadsdb Showing SPATIAL DATA To display the spatial data, we use the SELECT command. For example: SELECT id, AsText (Genome) U.S. Genome, name FROM ROADS_GEOM; We can use several operators in our query: www.hatma.info 3 WebgGIS with MapServer - MapLab - Chameleon - PostGIS o & & Say whether the outer boundary geometry of an object intersects / intersect with the boundary outer geometry of other objects. o ~ = Test whether the 2 pieces of geometry is identical. For example, if 'POLYGON ((0 0.1 1.1 0.0 0)) 'synonymous with' Polygon ((0 0.1 1.1 0.0 0)) '(yes). o = Test whether the outer limits of 2 pieces of geometry is identical Example: SELECT ID, NAME FROM ROADS_GEOM Genome WHERE ~ = GeomFromText ('LINESTRING (243,118.191108 191 232 243 242)', -1); SELECT ID, NAME FROM ROADS_GEOM WHERE GeomFromText Genome & & ('POLYGON ((191232 243117.191232 243119.191234 - 243,117.191232 243 117 ))',- 1); USING PGSQL2SHP We can use pgsql2shp to convert a table (or a particular spatial data on our database) into the ESRI file formats. shp pgsql2shp [<options>] <database> [<schema>.] <table> pgsql2shp [<options>] <database> <query> INDEX MAKE Spatial Index is used to speed data search. Using PostGIS GiST (Generalized Search Trees) indexes. CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] GIST_GEOMETRY_OPS); Table with 1 million barisdata, at 300MHz Solaris machine, requires computing waktgu 1 hr. www.hatma.info 4 WebgGIS with MapServer - MapLab - Chameleon - PostGIS Then execute the command to create / update the statistical geometry very importantly, to optimize query execution by the database. Analyze VACUUM [table_name] ([column_name]); Vacuum Database Should be done regularly, as in most PostgreSQL DBA with a cron job to do VACUUM periodically, to keep the index can function well in accelerating the query. Complex queries We can use the functions of geometry in our query command. Suppose function distance () is used to measure the distance between 2 pieces of geometry objects. SELECT the_geom FROM geom_table WHERE distance (the_geom, GeomFromText ('POINT (100 000 200 000)', -1)) <100 We can use the function expand () to find objects within a radius geometry of an object: SELECT * FROM GEOTABLE WHERE GEOCOLUMN & & Expand (GeomFromText ('POINT (1000 1000)', -1), 100) AND Distance (GeomFromText ('POINT (1000 1000)', -1), GEOCOLUMN) <100; Spatial functions can be seen in the manual / PostGIS reference. EXAMPLE IMPLEMENTATION Spatial SQL Create a table and its contents with the following command. CREATE TABLE bc_roads ( gid INTEGER PRIMARY KEY, name VARCHAR ) SELECT AddGeometryColumn ('public', 'bc_roads', 'the_geom', -1, 'LINESTRING', 2); www.hatma.info 5 WebgGIS with MapServer - MapLab - Chameleon - PostGIS insert into bc_roads values (1, 'FIRST STREET', GeomFromText ('LINESTRING (1 1, 3 2, 4 3)')); insert into bc_roads values (2, 'SECOND STREET', GeomFromText ('LINESTRING (2 4, 4 0)')); CREATE TABLE bc_municipality ( gid INTEGER PRIMARY KEY, code INTEGER UNIQUE, name VARCHAR ) SELECT AddGeometryColumn ('public', 'bc_municipality', 'the_geom', -1, 'MULTIPOLYGON', 2); insert into bc_municipality values (1, 101, 'AREA FIRST', GeomFromText ('MULTIPOLYGON (((1 1, 5 1, 5 5, 1 5, 1 1 )))')); insert into bc_municipality values (2, 102, 'SECOND AREA', GeomFromText ('MULTIPOLYGON (((4 4, 6 4, 6 6, 4 6, 4 4 )))')); insert into bc_municipality values (3, 103, 'AREA THIRD', GeomFromText ('MULTIPOLYGON (((2 2, 4 2, 4 4, 2 4, 2 2 )))')); insert into bc_municipality values (4, 104, 'AREA FOURTH', GeomFromText ('MULTIPOLYGON (( (2 2, 4 2, 4 4, 2 4, 2 2), (3 3, 4 3, 4 4, 3 4, 3 3))) ')); SIMPLE EXAMPLE OF THE PROBLEM 1. Seeking a total length of roads (in kilometers)? SELECT sum (length (the_geom)) / 1000 U.S. PANJANG_JALAN FROM bc_roads; 2. The location of the intersection of all roads with points (1 1) SELECT ASTEXT (intersection (the_geom, GeomFromText ('POINT (1 1)'))) FROM bc_roads; 3. Area Cities named 'AREA FIRST' (in hectares) SELECT area (the_geom) / 10 000 AS LUAS_HEKTAR FROM bc_municipality WHERE name = 'AREA FIRST'; 4. What is the biggest city? SELECT name, area (the_geom) / 10 000 AS LUAS_HEKTAR FROM bc_municipality LIMIT 1; www.hatma.info 6 WebgGIS with MapServer - MapLab - Chameleon - PostGIS Or SELECT max (area (the_geom) / 10000) U.S. LUAS_HEKTAR FROM bc_municipality; 5. How long is the road in each city? M.name SELECT, SUM (LENGTH (r.the_geom)) / 1000 U.S. KM_JALAN FROM bc_roads r, m bc_municipality WHERE Contains (m.the_geom, r.the_geom) AND m.the_geom & & r.the_geom GROUP BY m.name; 6. Create a new table with the contents: all roads in the city 'AREA FIRST' with additional column length was in all the cities, and the point of intersection with the city AREA FIRST. CREATE TABLE AS pg_roads SELECT r. *, ASTEXT (Intersection (r.the_geom, m.the_geom)) AS "Intersect IN:", LENGTH (r.the_geom) / 1000 AS "ORIGINAL LONG ROAD (KM)" FROM bc_roads r, m bc_municipality WHERE r.the_geom & & m.the_geom AND contains (m.the_geom, r.the_geom) AND m.name = 'AREA FIRST'; 7. How many kilometers long road "ONE WAY" in town "FIRST AREA"? SELECT SUM (LENGTH (r.the_geom)) / 1000 AS "KM length =" FROM bc_roads r, m bc_municipality WHERE m.name = 'AREA FIRST' AND r.name = 'ONE WAY' AND m.the_geom & & r.the_geom 8. Find the largest city that has a hole SELECT gid, area (the_geom) / 1000 AS 'AREA OF THE CITY " FROM bc_municipality WHERE nrings (the_geom)> 1 ORDER BY "CITY AREA" DESC LIMIT 1 SPATIAL OBJECT PostGIS supports all spatial objects be specified by the OpenGIS Consortium (Http://www.opengis.org) on the document for the Simple Features Specification for SQL (Http://www.opengis.org/techno/specs/99-049.pdf). PostGIS also develop capability with 3DZ, 3DM and 4D coordinates. WKB and time-out OpenGIS standard way to specify ada2 mengexpresikan spatial objects: the format Well-Known Text (timeout) and the Well-Known Binary (WKB). Examples of time-out: POINT (0 0) LINESTRING (0 0.1 1.1 2) Polygon ((0 0.4 0.4 4.0 4.0 0), (1 1, 2 1, 2 2, 1 2.1 1)) MultiPoint (0 0.1 2) MULTILINESTRING ((0 0.1 1.1 2), (2 3.3 2.5 4)) MULTIPOLYGON (((0 0.4 0.4 4.0 4.0 0), (1 1.2 1.2 2.1 2.1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1))) GEOMETRYCOLLECTION (POINT (2 3), LINESTRING ((2 3.3 4))) WebgGIS with MapServer - MapLab - Chameleon - PostGIS Input data in the database typically uses time-out format as above. However, data spatial database is stored in hexadecimal format. We can manipulate the format Input / Output, as follows: bytea WKB = asBinary (geometry); text timeout = asText (geometry); geometry = GeomFromWKB (bytea WKB, SRID); geometry = GeometryFromText (text time-out, SRID); NB: SRID = spatial referencing system identifier. insert query example: INSERT INTO SPATIALTABLE (THE_GEOM, THE_NAME) VALUES (GeomFromText ('POINT (-126.4 45.32)', 312), 'A Place'); CREATING A SPATIAL DATABASE The easiest way is to use pgAdmin, which is already available on the shortcut Windows start menu, the folder PostgreSQL. After pgAdmin starts, select Edit menu
New Objects New Database. Enter the database template choices: template_postgis TABLES AND TABLE GEOMETRY_COLUMNS SPATIAL_REF_SYS We will look at our database there are some tables that have been made. One of them SPATIAL_REF_SYS and GEOMETRY_COLUMNS. Which is used to store meta information of spatial objects that we make. SELECT * FROM spatial_ref_sys; SELECT * FROM geometry_columns; CREATING A SPATIAL TABLE The first step is to make the database as usual. For example: CREATE TABLE ROADS_GEOM (ID int4, NAME varchar (25)) WebgGIS with MapServer - MapLab - Chameleon - PostGIS The second step is to add a spatial column with AddGeometryColumn function. With the format: "SELECT AddGeometryColumn (<table_name>, <column_name>, <srid>, <type>, <dimension>). " For example: SELECT AddGeometryColumn ('public', 'roads_geom', 'Genome', 423, 'LINESTRING', 2); SELECT AddGeometryColumn ('roads_geom', 'Genome', 423, 'LINESTRING', 2); If you do not want to bother with SRID (Spatial Reference ID), give the value of SRID = -1. For example: CREATE TABLE roads (ROAD_ID int4, ROAD_NAME varchar (128)); SELECT AddGeometryColumn ('roads', 'roads_geom', -1, 'Geometry', 3); Note that the OGC geometries do not allow the value of Z or M. So that part dimension we just give the value 2. This section will review simply about PostGIS (spatial extension to PostgreSQL database). Database used: postgresql-8.2.3-1; on platform Windows INSTALLATION PostgreSQL - PostGIS There are various ways to build the PostgreSQL database that supports spatial data (PostGIS). But how easy it is to use the postgresql-8.2.msi (installer windows with full PostgreSQL features, including PostGIS) that can be downloaded at web site: http://www.postgresql.org. Then, do not forget to visit the site to PostGIS more complete information, including downloadable documentation / manual posGIS (http://www.postgis.org/)
MAINTAINING CONSISTENCY IN COLUMN SPATIAL DATA Make sure we meet the geometry column OpenGIS standard specification. One of them with the function IsValid (). For example: select IsValid ('LINESTRING (0 0, 1 1)'), IsValid ('LINESTRING (0 0.0 0)'); IsValid | IsValid ---------+--------- t | f There are ways to make us keep the data in order to remain compliant, ie by creating a check constraint. For example: ALTER TABLE mytable ADD CONSTRAINT CHECK geometry_valid_check (IsValid (the_geom)); check constraint will also be created automatically if you add a column geometric with AddGeometryColumn command (). LOADING spatial data There are several methods we can use. The first way is to use insert query command as usual. Suppose we create a file (roads.sql) containing: www.hatma.info 2 WebgGIS with MapServer - MapLab - Chameleon - PostGIS BEGIN; INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES (1, GeomFromText ('LINESTRING (243,118.191108 191 232 243 242)', -1), 'Jeff Rd'); INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES (2, GeomFromText ('LINESTRING (244,158.189265 189 141 244 817)', -1), 'Geordie Rd'); INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES (3, GeomFromText ('LINESTRING (228,138.192612 192 783 229 814)', -1), 'St. Paul'); INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES (4, GeomFromText ('LINESTRING (252,431.189631 189 412 259 122)', -1), 'Graeme Ave'); INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES- (5, GeomFromText ('LINESTRING (224,148.190871 190 131 228 134)', -1), 'Phil TCE'); INSERT INTO ROADS_GEOM (ID, Genome, NAME) VALUES- (6, GeomFromText ('LINESTRING (263,418.198213 198 231 268 322)', -1), 'Dave Cres'); COMMIT; Then execute the command on the "psql" SQL terminal monitor: psql-d [database]-f roads.sql USING SHP2PGSQL If we already have a shp file that contains our spatial data, easier using data shp2pgsql loader that will convert ESRI shp file into SQL. Shp2pgsql can we run on the "psql" SQL terminal monitor. For example: shp2pgsql shaperoads myschema.roadstable> roads.sql psql-d-f roadsdb roads.sql Or: shp2pgsql shaperoads myschema.roadstable | psql-d roadsdb Showing SPATIAL DATA To display the spatial data, we use the SELECT command. For example: SELECT id, AsText (Genome) U.S. Genome, name FROM ROADS_GEOM; We can use several operators in our query: www.hatma.info 3 WebgGIS with MapServer - MapLab - Chameleon - PostGIS o & & Say whether the outer boundary geometry of an object intersects / intersect with the boundary outer geometry of other objects. o ~ = Test whether the 2 pieces of geometry is identical. For example, if 'POLYGON ((0 0.1 1.1 0.0 0)) 'synonymous with' Polygon ((0 0.1 1.1 0.0 0)) '(yes). o = Test whether the outer limits of 2 pieces of geometry is identical Example: SELECT ID, NAME FROM ROADS_GEOM Genome WHERE ~ = GeomFromText ('LINESTRING (243,118.191108 191 232 243 242)', -1); SELECT ID, NAME FROM ROADS_GEOM WHERE GeomFromText Genome & & ('POLYGON ((191232 243117.191232 243119.191234 - 243,117.191232 243 117 ))',- 1); USING PGSQL2SHP We can use pgsql2shp to convert a table (or a particular spatial data on our database) into the ESRI file formats. shp pgsql2shp [<options>] <database> [<schema>.] <table> pgsql2shp [<options>] <database> <query> INDEX MAKE Spatial Index is used to speed data search. Using PostGIS GiST (Generalized Search Trees) indexes. CREATE INDEX [indexname] ON [tablename] USING GIST ([geometryfield] GIST_GEOMETRY_OPS); Table with 1 million barisdata, at 300MHz Solaris machine, requires computing waktgu 1 hr. www.hatma.info 4 WebgGIS with MapServer - MapLab - Chameleon - PostGIS Then execute the command to create / update the statistical geometry very importantly, to optimize query execution by the database. Analyze VACUUM [table_name] ([column_name]); Vacuum Database Should be done regularly, as in most PostgreSQL DBA with a cron job to do VACUUM periodically, to keep the index can function well in accelerating the query. Complex queries We can use the functions of geometry in our query command. Suppose function distance () is used to measure the distance between 2 pieces of geometry objects. SELECT the_geom FROM geom_table WHERE distance (the_geom, GeomFromText ('POINT (100 000 200 000)', -1)) <100 We can use the function expand () to find objects within a radius geometry of an object: SELECT * FROM GEOTABLE WHERE GEOCOLUMN & & Expand (GeomFromText ('POINT (1000 1000)', -1), 100) AND Distance (GeomFromText ('POINT (1000 1000)', -1), GEOCOLUMN) <100; Spatial functions can be seen in the manual / PostGIS reference. EXAMPLE IMPLEMENTATION Spatial SQL Create a table and its contents with the following command. CREATE TABLE bc_roads ( gid INTEGER PRIMARY KEY, name VARCHAR ) SELECT AddGeometryColumn ('public', 'bc_roads', 'the_geom', -1, 'LINESTRING', 2); www.hatma.info 5 WebgGIS with MapServer - MapLab - Chameleon - PostGIS insert into bc_roads values (1, 'FIRST STREET', GeomFromText ('LINESTRING (1 1, 3 2, 4 3)')); insert into bc_roads values (2, 'SECOND STREET', GeomFromText ('LINESTRING (2 4, 4 0)')); CREATE TABLE bc_municipality ( gid INTEGER PRIMARY KEY, code INTEGER UNIQUE, name VARCHAR ) SELECT AddGeometryColumn ('public', 'bc_municipality', 'the_geom', -1, 'MULTIPOLYGON', 2); insert into bc_municipality values (1, 101, 'AREA FIRST', GeomFromText ('MULTIPOLYGON (((1 1, 5 1, 5 5, 1 5, 1 1 )))')); insert into bc_municipality values (2, 102, 'SECOND AREA', GeomFromText ('MULTIPOLYGON (((4 4, 6 4, 6 6, 4 6, 4 4 )))')); insert into bc_municipality values (3, 103, 'AREA THIRD', GeomFromText ('MULTIPOLYGON (((2 2, 4 2, 4 4, 2 4, 2 2 )))')); insert into bc_municipality values (4, 104, 'AREA FOURTH', GeomFromText ('MULTIPOLYGON (( (2 2, 4 2, 4 4, 2 4, 2 2), (3 3, 4 3, 4 4, 3 4, 3 3))) ')); SIMPLE EXAMPLE OF THE PROBLEM 1. Seeking a total length of roads (in kilometers)? SELECT sum (length (the_geom)) / 1000 U.S. PANJANG_JALAN FROM bc_roads; 2. The location of the intersection of all roads with points (1 1) SELECT ASTEXT (intersection (the_geom, GeomFromText ('POINT (1 1)'))) FROM bc_roads; 3. Area Cities named 'AREA FIRST' (in hectares) SELECT area (the_geom) / 10 000 AS LUAS_HEKTAR FROM bc_municipality WHERE name = 'AREA FIRST'; 4. What is the biggest city? SELECT name, area (the_geom) / 10 000 AS LUAS_HEKTAR FROM bc_municipality LIMIT 1; www.hatma.info 6 WebgGIS with MapServer - MapLab - Chameleon - PostGIS Or SELECT max (area (the_geom) / 10000) U.S. LUAS_HEKTAR FROM bc_municipality; 5. How long is the road in each city? M.name SELECT, SUM (LENGTH (r.the_geom)) / 1000 U.S. KM_JALAN FROM bc_roads r, m bc_municipality WHERE Contains (m.the_geom, r.the_geom) AND m.the_geom & & r.the_geom GROUP BY m.name; 6. Create a new table with the contents: all roads in the city 'AREA FIRST' with additional column length was in all the cities, and the point of intersection with the city AREA FIRST. CREATE TABLE AS pg_roads SELECT r. *, ASTEXT (Intersection (r.the_geom, m.the_geom)) AS "Intersect IN:", LENGTH (r.the_geom) / 1000 AS "ORIGINAL LONG ROAD (KM)" FROM bc_roads r, m bc_municipality WHERE r.the_geom & & m.the_geom AND contains (m.the_geom, r.the_geom) AND m.name = 'AREA FIRST'; 7. How many kilometers long road "ONE WAY" in town "FIRST AREA"? SELECT SUM (LENGTH (r.the_geom)) / 1000 AS "KM length =" FROM bc_roads r, m bc_municipality WHERE m.name = 'AREA FIRST' AND r.name = 'ONE WAY' AND m.the_geom & & r.the_geom 8. Find the largest city that has a hole SELECT gid, area (the_geom) / 1000 AS 'AREA OF THE CITY " FROM bc_municipality WHERE nrings (the_geom)> 1 ORDER BY "CITY AREA" DESC LIMIT 1 SPATIAL OBJECT PostGIS supports all spatial objects be specified by the OpenGIS Consortium (Http://www.opengis.org) on the document for the Simple Features Specification for SQL (Http://www.opengis.org/techno/specs/99-049.pdf). PostGIS also develop capability with 3DZ, 3DM and 4D coordinates. WKB and time-out OpenGIS standard way to specify ada2 mengexpresikan spatial objects: the format Well-Known Text (timeout) and the Well-Known Binary (WKB). Examples of time-out: POINT (0 0) LINESTRING (0 0.1 1.1 2) Polygon ((0 0.4 0.4 4.0 4.0 0), (1 1, 2 1, 2 2, 1 2.1 1)) MultiPoint (0 0.1 2) MULTILINESTRING ((0 0.1 1.1 2), (2 3.3 2.5 4)) MULTIPOLYGON (((0 0.4 0.4 4.0 4.0 0), (1 1.2 1.2 2.1 2.1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1))) GEOMETRYCOLLECTION (POINT (2 3), LINESTRING ((2 3.3 4))) WebgGIS with MapServer - MapLab - Chameleon - PostGIS Input data in the database typically uses time-out format as above. However, data spatial database is stored in hexadecimal format. We can manipulate the format Input / Output, as follows: bytea WKB = asBinary (geometry); text timeout = asText (geometry); geometry = GeomFromWKB (bytea WKB, SRID); geometry = GeometryFromText (text time-out, SRID); NB: SRID = spatial referencing system identifier. insert query example: INSERT INTO SPATIALTABLE (THE_GEOM, THE_NAME) VALUES (GeomFromText ('POINT (-126.4 45.32)', 312), 'A Place'); CREATING A SPATIAL DATABASE The easiest way is to use pgAdmin, which is already available on the shortcut Windows start menu, the folder PostgreSQL. After pgAdmin starts, select Edit menu
New Objects New Database. Enter the database template choices: template_postgis TABLES AND TABLE GEOMETRY_COLUMNS SPATIAL_REF_SYS We will look at our database there are some tables that have been made. One of them SPATIAL_REF_SYS and GEOMETRY_COLUMNS. Which is used to store meta information of spatial objects that we make. SELECT * FROM spatial_ref_sys; SELECT * FROM geometry_columns; CREATING A SPATIAL TABLE The first step is to make the database as usual. For example: CREATE TABLE ROADS_GEOM (ID int4, NAME varchar (25)) WebgGIS with MapServer - MapLab - Chameleon - PostGIS The second step is to add a spatial column with AddGeometryColumn function. With the format: "SELECT AddGeometryColumn (<table_name>, <column_name>, <srid>, <type>, <dimension>). " For example: SELECT AddGeometryColumn ('public', 'roads_geom', 'Genome', 423, 'LINESTRING', 2); SELECT AddGeometryColumn ('roads_geom', 'Genome', 423, 'LINESTRING', 2); If you do not want to bother with SRID (Spatial Reference ID), give the value of SRID = -1. For example: CREATE TABLE roads (ROAD_ID int4, ROAD_NAME varchar (128)); SELECT AddGeometryColumn ('roads', 'roads_geom', -1, 'Geometry', 3); Note that the OGC geometries do not allow the value of Z or M. So that part dimension we just give the value 2. This section will review simply about PostGIS (spatial extension to PostgreSQL database). Database used: postgresql-8.2.3-1; on platform Windows INSTALLATION PostgreSQL - PostGIS There are various ways to build the PostgreSQL database that supports spatial data (PostGIS). But how easy it is to use the postgresql-8.2.msi (installer windows with full PostgreSQL features, including PostGIS) that can be downloaded at web site: http://www.postgresql.org. Then, do not forget to visit the site to PostGIS more complete information, including downloadable documentation / manual posGIS (http://www.postgis.org/)