David,
On 14/02/2008, at 3:00 PM, MAPSERVER-USERS automatic digest system
wrote:
Date: Wed, 13 Feb 2008 10:28:55 -0800
From: David Hipwell <[EMAIL PROTECTED]>
Subject: MapServer and Postgres/PostGIS
I'm a new MapServer user, and I've got a few questions about
connecting to
Postgres:
1. How do you connect to a postgres database, both locally
and over a
network,
You connect with a something like in your layer object in your mapfile :
CONNECTIONTYPE postgis
CONNECTION "host=localhost port=5432 dbname=bc password=postgres
user=postgres"
DATA "the_geom from (SELECT name,sum(length(the_geom)) AS
length,collect(geometryn(the_geom,1)) AS the_geom FROM bc_roads WHERE
the_geom && setsrid(!BOX!,3005) group by name order by length desc
limit 15) as foo using SRID=3005, using unique name"
You can use the results of the select query for classes etc in the
layer.
I guess if it is not a local connection, you just replace localhost
with whatever you want.
2. How do I pull spatial and non-spatial layers through the
connection, and
I'm not sure what you mean by this? Do you mean how do you get data
from the database? See above. I import any spatial layers which are
likely to be queried into postgres.
3. Is there any way to join the two tables based on a common
column.
Postgres supports the normal SQL Join Types... I might be missing
something here in your question?
Any help here would be a great asset.
I found at one stage an introduction / demonstration done by Jeff
McKenna, Adam Quiney, Chris Hodgson and or Paul Ramsey (and maybe
others) from Refractions - If you can find this it will step you
through using the benefits. To do with British Columbia Road maps and
is meant to slip into the ms4w package, although I had to butcher it
to work on Mac OSX. The data line above is from their map file.
Also, the PostGIS manual (on the refractions website) is very helpful
It is worth sticking with it, it is extraordinarily powerful.
cheers
Ben
--
Ben Madin
REMOTE INFORMATION
t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome WA 6725
[EMAIL PROTECTED]
Out here, it pays to
know...