Home > postgis, postgres > Creating a Spatial Table in PostGIS

Creating a Spatial Table in PostGIS

April 5th, 2007 rupert

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
Categories: postgis, postgres Tags: , ,
  1. rupert
    July 4th, 2007 at 06:16 | #1
    Reply | Quote

    You need to create spatial indexes on your queries to make it faster. As per postgis docs:

    To build a spatial index on a table with a geometry column, use the “CREATE INDEX” function as follows:

    CREATE INDEX [indexname] ON [tablename]
    USING GIST ( [geometrycolumn] );

    The “USING GIST” option tells the server to use a GiST (Generalized Search Tree) index.

    Note
    GiST indexes are assumed to be lossy. Lossy indexes uses a proxy object (in the spatial case, a bounding box) for building the index.

    You should also ensure that the PostgreSQL query planner has enough information about your index to make rational decisions about when to use it. To do this, you have to “gather statistics” on your geometry tables. For PostgreSQL 8.0.x and greater, just run the VACUUM ANALYZE command.

  2. rupert
    November 1st, 2007 at 02:49 | #2
    Reply | Quote

    http://pgrouting.postlbs.org/wiki/1.x/InstallationUbuntu710

    In Debian/Ubuntu, we need to add routing functions from pgRouting..

    # Add pgRouting functions
    psql -U postgres -f /usr/share/postlbs/routing_core.sql routing
    psql -U postgres -f /usr/share/postlbs/routing_core_wrappers.sql routing

Comments are closed.