Purpose of BostonGIS
BostonGIS is a testbed for GIS and Web Mapping solutions utilizing open source, freely available and/or open gis technologies. We will be using mostly Boston, Massachusetts data to provide mapping and spatial database examples.
If you have some thoughts or comments on what you would like to see covered on this site, drop us a line on our Feed Back page.
Article and Tutorial Comments
Boston GIS blog
PDF HTML All BostonGIS tutorials packaged together in an E-Book.
Boston GIS Store
pgRouting: Loading OpenStreetMap with Osm2Po and route querying
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.0)
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 1.5)
What Is PostGIS?
PostGIS is an open source, freely available, and
fairly OGC compliant spatial database extender for the PostgreSQL
Database Management System. In a nutshell it adds spatial functions
such as distance, area, union, intersection, and specialty geometry data types to the
database. PostGIS is very similar in functionality to SQL Server 2008 Spatial support, ESRI ArcSDE,
Oracle Spatial, and DB2 spatial extender. The latest release version
now comes packaged with the PostgreSQL DBMS installs as an optional
add-on. As of this writing PostGIS 1.5.0 is the latest stable release. NOTE: The windows stack builder installer now comes packaged with a Graphical User Interface version of ESRI Shapefile Loader that can be enabled as a PgAdmin III plugin.
We will assume a windows environment for this
tutorial, but most of the tutorial will apply to other supported
platforms such as Linux, Unix, BSD, Mac etc. We will also be using
Massachusetts/Boston data for these examples. For desktop users, the EnterpriseDB one-click installer exists as well for Mac/OSX and Linux desktops, so you should be able to follow along without too much fuss.
Installing PostgreSQL with PostGIS Functionality
We will not go into too much detail here since the
install wizard (at least the windows one) is pretty good. Below are the
Note for Vista Users Because of the new added security in Vista, you may run into issues installing PostgreSQL. Please refer to the Windows Vista gotchas http://trac.osgeo.org/postgis/wiki/UsersWikiWinVista in the PostGIS wiki if you run into issues.
- Download the install for your specific platform
from the PostgreSQL Binary Download ( http://www.postgresql.org/download/ ) . As of
this writing the latest version is PostgreSQL 8.4.2 and we will be assuming PostGIS 1.5. The below options follow the
basic sequence of the postgresql windows installer.
- Launch exe to install PostgreSQL
- If you want to access this server from other than
the server itself. Check the "Accept connection on all addresses, not
just localhost". NOTE: You can change this later by editing the
postgresql.conf -> listen_addresses property and if you don't
like the default port of 5432 you can change this as well in the
postgresql.conf -> port property.
- For encoding UTF-8 is preferred because you can
convert to other encodings. SQL_ASCII was the default on
Windows before 8.3 and was later replaced with WIN1252. UTF-8 however is now supported well under Windows and generally the default on Linux/Unix.
- For language make sure to check PL/pgsql. If you
forget, you can always use the createlang plpgsql command to install in
a specific database or in PgAdminIII navigate to Languages and select it.
- Once PostgreSQL is installed, launch Application Stack Builder from (Start->Programs->PostgreSQL 8.4->Applciation Stackbuilder and pick the version of PostgreSQL you want to install PostGIS on and the version of PostGIS to install. NOTE: PostGIS 1.4 and 1.5 can coexist on the same server so you can install both, but the last one you install will overwrite the template_postgis template database and the dumper and loader commandline tools in PostgreSQL bin folder. Generally speaking PostGIS 1.5.0 should work just fine everywhere you were using PostGIS 1.4 before
For Windows users running PostgreSQL 8.2, you will need to get the PostGIS installer from here Windows PostGIS installer maintained by Mark Cave-Ayland and Paragon (Leo and Regina). PostGIS 1.5 is not supported for PostgreSQL 8.2 and below, so please upgrade if you want to use the new features of PostGIS 1.5.
- Navigate to spatial extensions and pick PostGIS 1.5. Pick a mirror, download , install.
Please note for many install packages - particularly windows. When you choose PostGIS as an option, the system will create a template_postgis template database for you that has PostGIS functions included.
The create spatial database checkbox is optional, and we generally uncheck it. It creates a spatial database for you to experiment with, but the template_postgis is always created.
For those of you who want to try experimental builds -- e.g. 1.4.2 (that has preliminary bug fixes for 1.4 or you want to try the new cool features like WKT Raster or PostGIS 2.0 (will be up soon), we have experimental Windows builds made weekly or as frequently as anything interesting happens in the PostGIS code base. These can be downloaded from http://postgis.net/windows_downloads. For those who want to test out 1.4.2, just replace the postgis-1.4.dll in the PostgreSQL lib folder with the one in the zip and run the respective postgis upgrade sql file in the share/contrib/postgis-1.4.
Creating a spatial database
PostgreSQL comes packaged with a fairly decent admin
tool called PgAdmin3. If you are a newbie, its best just to use that
tool to create a new database.
- On windows PgAdmin III is under
Start->Programs->PostgreSQL 8.4->PgAdmin III
- Login with the super user usually postgres and the
password you chose during install. If you forgot it, then go into
pg_hba.conf (just open it with an editor such as notepad or a
programmer editor). Set the line
host all all 127.0.0.1/32 md5
host all all 127.0.0.1/32 trust
This will allow any person logging locally to the computer that
PostgreSQL is installed on to access all databases without a password.
(127.0.0.1/32) means localhost only (32 is the bit mask). Note you can
add additional lines to this file or remove lines to allow or block
certain ip ranges. The lines highest in the file take precedence.
So for example if you wanted to allow all users logging in access as long as they successfully authenticate with an md5 password, then you can add the line
. If it is below, you will still be able to connect locally without a password but non-local connections will need a valid username and password.
host all all 0.0.0.0/0 md5
Note: - The newer versions of PgAdmin III (1.7 something on) allow editing Postgresql.conf and pg_hba.conf using the PgAdmin III tool. These are accessible from Tools->Server Configuration and provide a fairly nice table editor to work with. This feature is only available if you have installed the adminpack.sql (this is located in C:\Program FIles\PostgreSQL\8.x\share\contrib) (Admin Pack) in the postgres database.
On windows the file is located in C:\Program Files\PostgreSQL\8.4\share\contribs\adminpack.sql. It is located in the share folder of Linux installs as well. To install it --- switch to postgres database and run the adminpack.sql script in that database.
- Now for the fun part - Create your database. Call
it gisdb or whatever you want. In newer versions of PostgreSQL, if you chose PostGIS in the first part, there is a template database called template_postgis. Chose this as a template.
- Its generally a good idea to create a user too that
owns the database that way you don't need to use your superuser account
to access it.
UPDATE: - The remaining steps in this section are not needed if you chose template_postgis for your new database. However if you are trying to spatially enable an existing database or you didn't get the template_postgis option. Do the remaining steps. Next go to tools->Query tool in pgAdmin III
and browse to the postgresql install contrib postgis.sql file (on
Windows the default install is Program
If you are trying to upgrade an already spatially enabled postgis database, then instead of running postgis.sql run one of the postgis_upgrade_*.sql files. So if for example you are upgrading a database from PostGIS 1.4 to 1.5 run postgis_upgrade_14_to_15.sql.
- On the Query tool, make sure you gisdb is selected
and then click the green arrow. You'll get a bunch of notices - not to
- Next open up the spatial_ref_sys.sql (on windows
Program files\Postgresql\8.4\share\contrib\postgis-1.5\spatial_ref_sys.sql. This step loads thousands of spatial reference
system records which is used by PostGIS Projection library to transform
from one spatial coordinate system to another.
As of PgAdmin III 1.10 -- the Plugin Icon has PSQL as an option and if you responded to the message Yes to the Overwrite my plugins.ini, you should get an additional PostGIS Shapefile and DBF Loader option. . This option should become ungreyed when you select a database and when you launch it, it will pass in the credentatials to the database for you and launch a PSQL connection.
If the Plugins green is disabled (and says No Plugins installed) then most likely you have another PgAdmin or PostgreSQL install getting in the way. An easy fix is to open up PgAdmin III go under File->Options and make sure your PG bin path is pointing at the locations of your PostgreSQL bin (e.g. C:\Program Files\PostgreSQL\8.4\bin) and click the ... to repoint it if it is not.
Loading GIS Data Into the Database
Now we have a nice fully functional GIS database with
no spatial data. So to do some neat stuff, we need to get some data to
Get the Data
Download data from the MassGIS site.
For this simple exercise just download Towns
Extract the file into some folder. We will only be using the _POLY
files for this exercise.
NOTE: Someone asked how you extract the file if you are on a linux box.
---FOR LINUX USERS ---
If you are on Linux/Unix, I find the exercise even easier.
If you are on linux or have Wget handy - you can do the below to download the file after you have cded into the folder you want to put it in.
Now to extract it simply do the following from a shell prompt
---END FOR LINUX USERS ---
NOTE: As of PostGIS 1.5.0, the windows build is now packaged with a shp2pgsql Graphical User Interface. You can alsodownload it separately if you are using a lower version of PostGIS or want to load data from a separate workstation that doesn't have PostgreSQL installed.download this separately and use with any version of PostGIS from 1.2 - 1.5.0 and enable it as a Plug-In in PgAdminIII . Check out our screencast on configurating the shp2pgsql-gui as a PgAdmin III plug-in and using it. or our write-up on registering it
Figure out SRID of the data
You will notice one of the files it extracts is called
TOWNS_POLY.prj. A .prj is often included with ESRI shape files and
tells you the projection of the data. We'll need to match this
descriptive projection to an SRID (the id field of a spatial ref record
in the spatial_ref_sys table) if we ever want to reproject our data.
- Open up the .prj file in a text editor. You'll see
something like NAD_1983_StatePlane_Massachusetts_Mainland_FIPS_2001
- Open up your PgAdmin III query tool and type in the
select srid, srtext, proj4text from
spatial_ref_sys where srtext ILIKE '%Massachusetts%' And
then click the green arrow. This will bring up about 10 records.
- Note the srid of the closest match. In this case
its 26986. NOTE: srid is not just a PostGIS term.
It is an OGC standard so you will see SRID mentioned a lot in other
spatial databases, gis webservices and applications. Most of the common
spatial reference systems have globally defined numbers. So 26986
always maps to NAD83_StatePlane_Massachusetts_Mainland_FIPS_2001
Meters. Most if not all MassGIS data is in this particular projection.
Loading the Data
The easiest data to load into PostGIS is ESRI shape
data since PostGIS comes packaged with a nice command line tool called
shp2pgsql which converts ESRI shape files into PostGIS specific SQL
statements that can then be loaded into a PostGIS database.
This file is located in the PostGresql bin folder
which default location in Windows is Program
Make a PostGIS mini toolkit
Since these files are so embedded, it is a bit
annoying to navigate to. To create yourself a self-contained toolkit
you can carry with you anywhere, copy the following files from the bin
folder into say c:\pgutils:
comerr32.dll krb5_32.dll libeay32.dll
libiconv-2.dll libintl-2.dll libpq.dll pgsql2shp.exe psql.exe
pg_dump.exe pg_restore.exe shp2pgsql.exe ssleay32.dll
Note: The GUI loader is packaged as a self-contained postgisgui folder in the bin of your PostgreSQL install. If you prefer the GUI interface, you can copy that folder and run the shp2pgsql-gui.exe file from anywhere even an external file network path.
Load Towns data
- Open up a command prompt.
- Cd to the folder you extracted the towns data
- Run the following command:
c:\pgutils\shp2pgsql -s 26986 TOWNS_POLY towns > towns.sql
- Load into the database with this command:
psql -d gisdb -h localhost -U postgres -f towns.sql
If you are on another machine different from the server, you will need
to change localhost to the name of the server. Also you may get
prompted for a password. For the above I used the default superuser
postgres account, but its best to use a non-super user account.
- Alternatively you can use the gui to load the data and when you do, your screen will look something like this.
Indexing the data
Table indexes are very important for speeding up the
processing of most queries. There is also a downside to indexes and
they are the following
- Indexes slow down the updating of indexed fields.
- Indexes take up space. You can think of an index as
another table with bookmarks to the first similar to an index to a book.
Given the above, it is often times tricky to have a
good balance. There are a couple general rules of thumb to go
by that will help you a long way.
- Never put indexes on fields that you will not use
as part of a where condition or join condition.
- Be cautious when putting index fields on heavily
updated fields. For example if you have a field that is
frequently updated and is frequently used for updating, you'll need to
do benchmark tests to make sure the index does not cause more damage in
update situations than it does for select query situations.
In general if the number of records you are updating at any
one time for a particular field is small, its safe to put in an index.
- Corrollary to 2. For bulk uploads of a
table - e.g. if you are loading a table from a shape, its best to put
the indexes in place after the data load because if an index
is in place, the system will be creating indexes as its loading which
could slow things down considerably.
- If you know a certain field is unique in a table, it
is best to use a unique or primary index. The reason for this is
that it tells the planner that once its found a match, there is no need
to look for another. It also prevents someone from accidentally
inserting a duplicate record as it will throw an error.
- For spatial indexes - use a gist index. A gist basically
stores the bounding box of the geometry as the index. For large complex
geometries unfortunately, this is not too terribly useful.
The most common queries we will be doing on this query
are spatial queries and queries by the town field. So we will create 2
indexes on these fields.
CREATE INDEX idx_towns_the_geom
CREATE INDEX idx_towns_town
Go back into PgAdmin III and refresh your view. Verify
that you have a towns database now.
Test out the following queries from the query tool
For PostGIS installations of 1.2.2 and above, the preferred function names start with ST_
SELECT ST_Extent(the_geom) FROM towns WHERE town = 'BOSTON';
SELECT ST_Area(ST_Union(the_geom)) FROM towns WHERE town = 'BOSTON';
Old syntax pre PostGIS 1.2.2 - this will not work in PostGIS 1.4+. If you have old code like this -- change it to the above syntax. We have crossed out the below code to demonstrate it is BAD
SELECT Extent(the_geom) from towns where town = 'BOSTON';
SELECT Area(GeomUnion(the_geom)) FROM towns where town = 'BOSTON';
Most functions in new postgis installs just have an ST_ prefixed, except for GeomUnion which became ST_Union. The other difference is that relational operators with ST_ now automagically use index operators where as the ones without ST_ you need to do an additional && call.
a.the_geom && b.the_geom AND Intersects(a.the_geom,b.the_geom)
can simply be written as
If the above gives you an error such as mixed SRIDs, most likely you are running 1.3.2 postgis which was very defective. Upgrade to 1.3.3 at your next opportunity. To verify your install -
Viewing the Data
If you are a GIS newbie, I highly recommend using Quantum GIS. Quantum GIS has ability to view PostGIS data directly, do simple filters on it, is free, is cross-platform (Linux, and Windows and some others) and is the least threatening of all the GIS Viewers I have seen out there for people new to GIS.
Post Comments About Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 1.5)
OSCON 2009: Tips and Tricks for Writing PostGIS Spatial Queries
PGCon2009: PostGIS 1.4, PostgreSQL 8.4 Spatial Analysis Queries, Building Geometries, Open Jump
PLR Part 3: PL/R and Geospatial Data Abstraction Library (GDAL) RGDAL
PostGIS Nearest Neighbor: A Generic Solution - Much Faster than Previous Solution
Solving the Nearest Neighbor Problem in PostGIS
PLR Part 2: PL/R and PostGIS
PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot's Guide
Part 2 - PostGIS and SharpMap in ASP.NET 2.0 using VB.NET: Displaying the Maps
Part 1 - PostGIS and SharpMap in ASP.NET 2.0 using VB.NET: Compiling SharpMap with PostGIS
Part 3: PostGIS Loading Data from Non-Spatial Sources
Part 2: Introduction to Spatial Queries and SFSQL with PostGIS
|4/29/2013 8:45:24 AM||jan kordaat||looking for info
|11/29/2012 10:47:38 AM||n/a
|2/17/2011 9:03:28 AM||Regina||Christophe,
Which version of PostGIS are you using?
Probably best to subscribe to PostGIS users newsgroup.
You really learn a lot by reading what others have to say and many more people to help out.
Off-hand from your steps, I can't tell what you are doing wrong.
return anything for you. If not -- then you don't have PostGIS installed
|2/16/2011 1:27:13 PM||Christophe ||Glad to see all the comments.
I'm using the latest of both PostGIS and pgAdminIII (just downloaded this week). I'm trying to follow the tutorial. I can't seem to load the data. This is my first time using this, so I'm a pre-newbie.
1. CREATE A SPATIAL DATABASE. I created a new database from the template (I highlighted template_postgis, right-clicked, chose New Object, New Database, and named it gisdb, as suggested.)
2. I used the Query tool for the postgis.sql to spatially enable the database (even though I based the new db on the template, I needed to do this step.)
3. I used the Query tool again, to run the spatial_ref_sys.sql. I got some messages, but not sure if they were alarming.
4. FIGURE OUT THE SRID. I used the Query tool a third time to get the SRID. So far so good... except that the SRID number comes up -1. Also, when I press "connect" I get a succeeded message. But when I try to "import" the data, I get a "failed" message, and then a fatal error message and the GUI interface closes.
5. MAKE A POSTGIS MINI TOOL KIT. Done, though my libintl-2.dll is a libintl-8.dll. Not sure if that makes a difference. (I'm using PostgreSQL Version 9)
6a. LOAD TOWN DATA. Now I'm at the part where I should be able to load the data, but so far no luck. I opened up a DOS command prompt. I used the CD command to change directories and got to my Towns data. At the command prompt, I simply typed the following: c:\pgutils\shp2pgsql -s 26986 TOWNS_POLY towns > towns.sql. I didn't use any command, but simply hit enter. I didn't get an error.
6b. At the command prompt again, I typed: psql -d gisdb -h localhost -U postgres -f towns.sql I got an error message. Not sure what command I should use.
6c. Alternatively, I tried the GUI Shapefile loader again. Still no luck. Fatal error, closes the GUI.
So for me, there is not yet any data, so no INDEXING THE DATA, no QUERYING THE DATA.
Perhaps someone can point me in the right direction. Meanwhile, I'm going to back-up, again, and keep trying to figure this out.
|2/8/2011 8:48:14 AM||Regina||This is probably best asked on the PostGIS newsgroup --
To answer since you asked
No specific entity owns it. It is owned by all the developers that have ever worked on it. So I guess you can say its a shared copyright.
It was started by Refractions Research when Paul Ramsey was president and the main website is still managed and hosted by Refractions (source control and user wiki is hosted by OSGEO). Paul moved to OpenGeo and that's where you see the intimate tie in of PostGIS in OpenGeo. Neither Refractions nor OpenGeo owns it since copyright licenses for work done were not required to be transferred from each developer to Refractions (unlike MySQL that is wholly owned by Oracle and before Sun and before MySQL AB because developers were forced to hand over copyright to the MySQL company for their work to be included). So though its GPL, that's the difference.
As far as management goes on who makes decisions about its direction, its managed by a steering committee of which I'm a member of, and none of us come from the same company.
Listing of current steering committee members is here:
|2/8/2011 8:19:05 AM||parisjetaime||I am sorry for posting this here if its inappropriate but Who currently owns and develops PostGIS? i have seen it mentioned on both OpenGeo and Refractions website. Its also mentioned on OSGEO website.
|1/18/2011 7:53:26 PM||Regina||Jan,
That error means you don't have PostGIS enabled in your database. If you are running on windows, the easiest way is to create the database using the template template_postgis database (instead of template1).
If you already have your database created -- just run the postgis scripts.
For 1.5 you will find it in /share/contrib/postgis-1.5 -- run the postgis.sql and spatial_ref_sys.sql files.
It is all documented here:
|1/17/2011 12:14:03 PM||Jan||I am trying to create a table using postgreSQL 8.4: CREATE TABLE observation ( (...)
but I get this error message:
ERROR: type "geometry" does not exist
Does anyone know how to solve it?
Thanks in advance.
|10/24/2010 11:32:58 AM||Francis||RAS
|7/9/2010 5:28:11 PM||naveed||the tutorial is not sequential and ones cannot get a good support from this tutorial, it should be simple precise and reasonably good
|6/30/2010 11:05:10 AM||azhar||I would like to know how i can use PostGIS
|5/25/2010 9:06:15 AM||Regina||Yes the indexes are automatically created if you check the "Create spatial index automatically" checkbox option in the Options window.
|5/19/2010 3:54:34 PM||GW||If you use the shp file loader, are the indexes automatically created for you? Thanks!
|4/26/2010 11:01:49 PM||Regina||Nazish,
What do you want to know specifically? You mean who uses it?
Some case studies are here:
|4/24/2010 8:14:43 AM||nazish||I am newbie in GIS I want to know PostGIS usage.
|4/3/2010 8:05:21 AM||vaibhav||I want more on mapguide in sequentai order.
|2/12/2010 9:12:32 PM||Regina||Vivian,
You may want to try out the new PostGIS GUI loader. You can install it as a Plug-In in PgAdmin III. It also doesn't have that upper case issue that SPIT has.
We've updated this article detailing this.
|2/9/2010 9:46:30 AM||Vivian||I have found this tutorial very useful and succeeded in importing a large shape after adding the optional command -W utf8 and selecting the specific .shp file instead of just typing the .shp file title.... In the end I found www.gisvm.com a virual machine where PostGIS is already setup and Q-Gis installed. Q-GIS comes with a tool called SPIT. The combination of PostGIS and Q-GIS (SPIT) allows to import and view data from and two a PostGIS database. (The full idiot way: 100% no terminal commands)
|1/30/2010 2:11:42 PM||Regina||Garret,
Guess we have to update this. Use ST_Union instead and see if that works.
|1/29/2010 1:45:28 AM||Garret||Hi, great tutorial. Its exactly what I have been looking for. One question...
When using your select statement:
SELECT Area(geomunion(the_geom)) FROM towns where town = 'BOSTON';
I get the error:
ERROR: function geomunion(geometry) does not exist
LINE 1: SELECT Area(geomunion(the_geom)) FROM towns where town = 'BO...
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********
ERROR: function geomunion(geometry) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Any thoughts on why I might be getting this error?
|1/6/2010 3:34:03 PM||regragui||je desire tous connaitre sur postgis
|11/21/2009 1:52:56 AM||Deepak||The is site is good one
|11/8/2009 10:03:39 PM||Leo||Jeff,
You can automate a commandline but you can't so easily a gui. A sophisticated commandline interface is much faster to create than a gui, so you will find most GUIs are just wrappers around command line engines.
Even microsoft has beefed up their command line -- e.g. Windows Powershell.
So while the gui has added a lot of useful enhancements to the end user, it has not replaced the commandline.
|11/5/2009 8:42:32 PM||Jeff||I know this is not the author's fault, but is it just me, or didn't we leave the DOS era around 20 years ago? This stuff is obviously powerful, and apparently, the tool to use (me being a newbie and all). But I am in disbelief in seeing this and postgreSQL keeping the power locked behind a command prompt interface with parameters.
Is there some convincing reason why this whole process is reminiscent of the stone ages?
|9/14/2009 8:26:44 PM||Gerald Buckmaster||Very good tutorial. Informative. You might want to add a note about not using non-special character passwords for postgres user as postgis 1.4 doesn't like/authenticate them during installation. I also saw this in a bug report.
|9/7/2009 8:33:15 AM||Dan||Leo,
Thanks so much. I did some rethinking based on your suggestion and I came up with a solution on my 'site' table. Maybe others are interested to see the script, comment or correct it (i'm just a postgres novice).
--First, I added a geometry column to the site (in WGS 84, 2-D point);
SELECT AddGeometryColumn( 'public', 'site', 'sitepoint_lonlat', 4326, 'POINT', 2 );
--Second, I populate the geometry column
UPDATE site SET sitepoint_lonlat = PointFromText('POINT(' || site_lon || ' ' || site_lat || ')',4326);
--Then I wrote a trigger to insert or update into the geometry column whenever a new site with latitude and longitude is entered or the previous are updated
CREATE OR REPLACE Function sitepoint_conv() RETURNS TRIGGER AS
IF (TG_OP = 'INSERT') THEN
sitepoint_i = PointFromText('POINT ('|| new.site_lon || ' ' || new.site_lat ||')', 4326);
UPDATE site SET sitepoint_lonlat = sitepoint_i WHERE site_nb = new.site_nb;
ELSIF (TG_OP = 'UPDATE') THEN
IF new.site_lon <> old.site_lon OR new.site_lat <> old.site_lat THEN
sitepoint_ua = PointFromText('POINT ('|| new.site_lon || ' ' || new.site_lat ||')', 4326);
UPDATE site SET sitepoint_lonlat = sitepoint_ua where site_nb = old.site_nb;
CREATE TRIGGER sitepoint_conv
AFTER INSERT OR UPDATE
ON site FOR EACH ROW
EXECUTE PROCEDURE sitepoint_conv();
|9/5/2009 5:08:45 PM||Leo||Dan,
Given you constraints, your solution sounds fine. To ensure your SiteTableB is updated when a user adds new data to SiteTableA, I would put a PostgreSQL trigger on SiteTableA. We do that with our linked tables in MS Access and seems to work just fine. http://www.postgresql.org/docs/8.4/interactive/sql-createtrigger.html
Yes you should put in a foreign key, primary key then you can implement things like cascade update/delete between the two tables. If you have a one to one, you can make the same field a primary key in both tables and still have a foreign key relationship between the sitea and siteb tables.
|9/3/2009 3:58:12 AM||Mark||just getting started!!
|8/31/2009 5:58:57 AM||Dan||Leo,
Thanks for your reply and your suggestions. Actually, what I really want is more complicated than just a single table. I built a postgresql postgis-enabled database with Ms Access as front-end for the non-spatial tables and Mapserver or Quantum GIS (future) for the Spatial objects. I have a feature 'Site'. I built 2 tables, 'SiteTableA' is the non-spatial table (without the geometry column) that the user used to enter site names and coordinates and is viewable in Ms Access. 'SiteTableB' is the spatial object table containing the geometry column + all features of SiteTableA, viewable in Mapserver or Quantum GIS. These tables will be related to other non-spatial tables in the database (when users query make query in Access or MapServer) for instance to the PopulationTable where each site has yearly population. My first concern is whether this design for Site is optimum. Second how can i ensure that 'SiteTableB' is updated (esp. the geometry column) whenever I update or add new data in 'SiteTableA' using Ms Access. Third, do I have to specify a CONSTRAINT ( primary key and foreign key relationship) between spatial and non-spatial tables (ei. SiteTableB and SiteTableA, SiteTableB and PopulationTable)? Thanks for any feedback.
|8/9/2009 3:34:23 AM||Leo||Dan,
Keep in mind if you have a table with spatial data, you can have attribute data in it as well.
So for your sites case -- it sounds like all you need is one table
sites -- has geometry, count_of_people, site_id, site_name
or something like that.
If you are asking if you keep each person detail separately then you can have a people table with site_id and then just relate by site_id
SELECT sites.site_id, sites.site_name, COUNT(p.people_id) As total_people, sites.the_geom
FROM sites LEFT JOIN people As p ON sites.site_id = p.site_id
GROUP BY sites.site_id, sites.site_name;
If you are talking about relating by proximity, then you would join by ST_DWithin.
|7/30/2009 9:52:49 AM||Dan||Very helpful tutorial, indeed. Just some question, If I have a postgis-enabled database and I want to put both spatial and non-spatial features, how can I build relationship between them? For instance, I want to relate the spatial features sites(points) to non-spatial table,population, so that the SQL be valid? Basically, in the future I want to know/map which sites contain how many people.
|6/9/2009 3:22:43 PM||Mike Vella||One of the best tutorials I have ever read, about anything!
|5/10/2009 4:00:57 PM||Leo||Ronald --
Read this FAQ: http://trac.osgeo.org/postgis/wiki/UsersWikiNewbieAddgeometrycolumn
this usually means you don't have PostGIS lwpostgis.sql installed or you didn't use a template_postgis database. Try running lwpostgis.sql again and verify you are not getting any errors when running it. The lwpostgis.sql is located in share/contrib/ or share/contrib/postgis folder of your PostgreSQL install. Also verify you have liblwgeom.dll (on windows) liblwgeom.so (on Unix/Linux) in your postgresql/lib folder if you are getting errors on the lwpostgis.sql install.
It is also possible you have a typo in your AddGeometryColumn statement. You didn't post your statement so its hard to tell.
|5/7/2009 9:16:35 AM||Amresh||Excellent & very useful.
thanx for nice document
|5/6/2009 7:05:51 AM||ronald cho||hi,
hope you can help me with this.
i have followed all the steps in your guide until loading data into the spatial DB.
i keep getting this error:
psql:test.sql:7: ERROR: function addgeometrycolumn(unknown, unknown, unk
nown, unknown, unknown, integer) does not exist
LINE 1: SELECT AddGeometryColumn('shape','test,'the_geom','2...
HINT: No function matches the given name and argument types. You might need to
add explicit type casts.
i have checked the DB and the function is there, 3 of them actually.
and all privileges are also granted.
|4/29/2009 11:16:42 AM||Laurence||I'm another idiot who was very happy to find this website! Thanks a lot!
|3/17/2009 9:49:08 AM||Laura||excellent
|3/15/2009 8:01:55 PM||Leo||Doug,
Not sure I quite understand your question. So let me guess.
CD is a shell command and works in both Unix and Windows. So yes you would need to be at a Dos or linux shell prompt.
For windows -- you can copy the command in a batch script. With .bat extension and I usually put a pause at the end of the script so that you can then double-click on the batch file from windows explorer and the DOS window will stay up so you can see the status.
|3/11/2009 2:56:22 AM||DougK||how do you "Cd to the folder"? Cd, is that a DOS thing. Is there are way to run a text file instead of retyping the shp2sql command over and over to see if it works?
|3/6/2009 6:08:52 AM||sam||The tutorials were amazing.... Thanks a lot for your efforts.
|11/11/2008 7:55:43 PM||Karthik||this is an essential one
|5/31/2008 5:21:47 PM||Pierre||Thanks for this very useful and clear tutorial. Good job!
|5/21/2008 4:02:16 AM||Regina||Chris Schmidt and Howard Butler put out a very useful site called
This site contains both standard spatial reference systems and user contributed ones. Each also includes the postgis insert statement to create and a simple search page to search for them.
|11/24/2007 3:41:28 PM||Kenton Williams||On your PostGIS Almost Idiot's guide, you refer to searching for an SRID. You may wish to spell this out more clearly for newbies. Spatial Reference ID (SRID), commonly called a projection. It isn't specified anywhere, but you may also wish to mention that the SRID codes are the EPSG codes that correspond to predefined Spatial Reference systems (projections). When using tools such as FWTools' command-line programs, you can specify source and destination projections using EPSG codes. It used to be a little easier to find these codes if you didn't know them, but EPSG took down a really useful page that allowed browsing down a list. So, for example, a person could reproject a raster from one Spatial Reference System (SRS) to another using gdalwarp and specifying the source and to projections using these EPSG/SRID codes. Using PostGIS and a SQL query to find the code you need is really helpful and much easier than digging around on the internet! Thanks for your reference, by the way - I'm not used to running PostgreSQL and your guide took away some trial-and-error.