RE: [mapserver-users] Trouble connecting to PostGIS

2012-02-13 Thread Bistrais, Bob 
I think we're out of the woods, and special thanks to TC Haddad for the
solution...

 

TC suggested that I check the upper/lower case of the table name.  I had
this correct, but it gave me an idea of something to try- I created a
new table of the same shapefile, only this time the table name is all
lower case.  Using that in my map file, the map image appears correctly.

 

Thanks everyone for their input!  I'm sure it won't be the last ou hear
from me on PostGIS, but at least we're off and running!

 

BB

 

 



From: pcr...@pcreso.com [mailto:pcr...@pcreso.com] 
Sent: Monday, February 13, 2012 5:01 PM
To: mapserver-users@lists.osgeo.org; Bistrais, Bob
Subject: Re: [mapserver-users] Trouble connecting to PostGIS

 

Hi Bob,


I suggest you try testing with QGIS.

Easy to open the native postgis table & a mapserver WFS/WMS layer
concurrently to ensure it is all working as it should.

Also ensure you have configured your Postgis (Postgres) database to
allow applications such as mapserver to connect. For testing purposes,
set access to the database to trusted for all in pb_hba.conf, & enable
external connections in postgresql.conf, then restart the server to
ensure the changes are activated.

You can also enable mapserver debugging & specify a log file to see what
error is reported.
http://mapserver.org/optimization/debugging.html

You may also need to specify the unique ID column name & the SRID you
are using in your DATA string, much like this one:
DATA "track_line from (select * from trip_track where trip_code =
'ind0701') as foo using unique id using srid 4326"

HTH,

   Brent Wood

--- On Tue, 2/14/12, Bistrais, Bob  wrote:


From: Bistrais, Bob 
Subject: [mapserver-users] Trouble connecting to PostGIS
To: mapserver-users@lists.osgeo.org
Date: Tuesday, February 14, 2012, 10:11 AM

First of all, let me state that I am brand new to PostGIS and don't know
much about what I'm doing with it.  That being said, I am having trouble
displaying a PostGIS layer through MapServer.

 

Right now I'm just experimenting with PostGIS and simply trying to get a
layer to display.  I installed the Postgre and PostGIS 1.5.  I used the
shapefile loader to convert a couple shapefiles to PostGIS data.
Looking in pgAdmin, I see the tables do indeed exist for these layers.

 

So I built a MapServer map file.  Here is my connection strings:

 

DATA "the_geom from MylayerPoly"

CONNECTION "user=postgisuser password=MyPassword! dbname=postgistest
host=localhost port=5432"

CONNECTIONTYPE postgis

 

But the layer does not display.  Incidentally, I'm using GeoMoose as a
front end for testing, but that shouldn't matter here.

 

I did a mapserv -v at commandline, and I see the parameter
INPUT=POSTGIS.

 

So as far as I know it should work. Can anyone offer suggestions as to
what I'm doing wrong?

 

Thanks,

Bob


-Inline Attachment Follows-

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

 

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

RE: [mapserver-users] Trouble connecting to PostGIS

2012-02-13 Thread Bistrais, Bob 
I changed my Data statement similar to what you did here, Brent, and it
still comes up with an error, but the message shows that there is
something wrong with the select statement, so maybe this is another
clue:

 

prepare_database(): Query error. Error declaring cursor: ERROR:  syntax
error at or near "select"

LINE 1: ..._2d(the_geom)),'NDR'),gid::text from Metwp100Poly(select *
f...

 ^

 

With query string: DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from
Metwp100Poly(select * from Metwp100Poly where land='y') as foo WHERE
the_geom && setSRID('BOX3D(134000 4692598.18523154,890900
5307401.81476846)'::BOX3D, 26919 )

 



From: pcr...@pcreso.com [mailto:pcr...@pcreso.com] 
Sent: Monday, February 13, 2012 5:01 PM
To: mapserver-users@lists.osgeo.org; Bistrais, Bob
Subject: Re: [mapserver-users] Trouble connecting to PostGIS

 

Hi Bob,


I suggest you try testing with QGIS.

Easy to open the native postgis table & a mapserver WFS/WMS layer
concurrently to ensure it is all working as it should.

Also ensure you have configured your Postgis (Postgres) database to
allow applications such as mapserver to connect. For testing purposes,
set access to the database to trusted for all in pb_hba.conf, & enable
external connections in postgresql.conf, then restart the server to
ensure the changes are activated.

You can also enable mapserver debugging & specify a log file to see what
error is reported.
http://mapserver.org/optimization/debugging.html

You may also need to specify the unique ID column name & the SRID you
are using in your DATA string, much like this one:
DATA "track_line from (select * from trip_track where trip_code =
'ind0701') as foo using unique id using srid 4326"

HTH,

   Brent Wood

--- On Tue, 2/14/12, Bistrais, Bob  wrote:


From: Bistrais, Bob 
Subject: [mapserver-users] Trouble connecting to PostGIS
To: mapserver-users@lists.osgeo.org
Date: Tuesday, February 14, 2012, 10:11 AM

First of all, let me state that I am brand new to PostGIS and don't know
much about what I'm doing with it.  That being said, I am having trouble
displaying a PostGIS layer through MapServer.

 

Right now I'm just experimenting with PostGIS and simply trying to get a
layer to display.  I installed the Postgre and PostGIS 1.5.  I used the
shapefile loader to convert a couple shapefiles to PostGIS data.
Looking in pgAdmin, I see the tables do indeed exist for these layers.

 

So I built a MapServer map file.  Here is my connection strings:

 

DATA "the_geom from MylayerPoly"

CONNECTION "user=postgisuser password=MyPassword! dbname=postgistest
host=localhost port=5432"

CONNECTIONTYPE postgis

 

But the layer does not display.  Incidentally, I'm using GeoMoose as a
front end for testing, but that shouldn't matter here.

 

I did a mapserv -v at commandline, and I see the parameter
INPUT=POSTGIS.

 

So as far as I know it should work. Can anyone offer suggestions as to
what I'm doing wrong?

 

Thanks,

Bob


-Inline Attachment Follows-

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

 

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Trouble connecting to PostGIS

2012-02-13 Thread Brent Fraser 
And I seem to recall needing to give some privileges on the 
spatial_ref_sys and geometry_columns  tables to the user.


Best Regards,
Brent Fraser


On 2/13/2012 3:05 PM, Puneet Kishor wrote:

On Feb 13, 2012, at 3:35 PM, Bistrais, Bob wrote:


Thanks Brent.  The postgis user SHOULD have access to the table, as that
is the table's owner.  Coordinate system is in the map file under
Projection.



Here is what the debug log showed me (Metwp100Poly is the actual name of
the table, and MAINE is the layer name):



[Mon Feb 13 16:31:32 2012].453000 CGI Request 1 on process 1300

[Mon Feb 13 16:31:32 2012].687000 msPOSTGISLayerRetrievePGVersion():
Query error. Error executing POSTGIS statement
(msPOSTGISLayerRetrievePGVersion():select substring(version() from 12
for (position('on' in version()) - 13))

[Mon Feb 13 16:31:32 2012].687000 prepare_database(): Query error. Error
declaring cursor: ERROR:  relation "metwp100poly" does not exist
..

The error above is telling you that it can't even find the said table.

Can you SELECT from this table successfully in PgAdmin?

Is it in a different schema than public, and perhaps that schema is not in the 
searchpath?

Does the postgisuser have access to this table?

--
Puneet Kishor___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users



___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Trouble connecting to PostGIS

2012-02-13 Thread Jeff McKenna 

On 12-02-13 6:05 PM, Puneet Kishor wrote:

The error above is telling you that it can't even find the said table.

Can you SELECT from this table successfully in PgAdmin?

Is it in a different schema than public, and perhaps that schema is not in the 
searchpath?

Does the postgisuser have access to this table?



For any dataset, I always always always (did I say always?) start with 
an ogrinfo command, to test the connection (in this case the connection 
to PG).  The syntax is documented on that same page: 
http://www.mapserver.org/input/vector/postgis.html  I don't ever move to 
MapServer testing before ogrinfo is happy.  (hint: you can pass SQL 
queries to your database through ogrinfo with the -sql switch)


-jeff




--
Jeff McKenna
MapServer Consulting and Training Services
http://www.gatewaygeomatics.com/


___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Trouble connecting to PostGIS

2012-02-13 Thread Puneet Kishor 

On Feb 13, 2012, at 3:35 PM, Bistrais, Bob wrote:

> Thanks Brent.  The postgis user SHOULD have access to the table, as that
> is the table's owner.  Coordinate system is in the map file under
> Projection.
> 
> 
> 
> Here is what the debug log showed me (Metwp100Poly is the actual name of
> the table, and MAINE is the layer name):
> 
> 
> 
> [Mon Feb 13 16:31:32 2012].453000 CGI Request 1 on process 1300
> 
> [Mon Feb 13 16:31:32 2012].687000 msPOSTGISLayerRetrievePGVersion():
> Query error. Error executing POSTGIS statement
> (msPOSTGISLayerRetrievePGVersion():select substring(version() from 12
> for (position('on' in version()) - 13))
> 
> [Mon Feb 13 16:31:32 2012].687000 prepare_database(): Query error. Error
> declaring cursor: ERROR:  relation "metwp100poly" does not exist
> ..

The error above is telling you that it can't even find the said table. 

Can you SELECT from this table successfully in PgAdmin?

Is it in a different schema than public, and perhaps that schema is not in the 
searchpath?

Does the postgisuser have access to this table?

--
Puneet Kishor___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Trouble connecting to PostGIS

2012-02-13 Thread pcreso 
Hi Bob,


I suggest you try testing with QGIS.

Easy to open the native postgis table & a mapserver WFS/WMS layer concurrently 
to ensure it is all working as it should.

Also ensure you have configured your Postgis (Postgres) database to allow 
applications such as mapserver to connect. For testing purposes, set access to 
the database to trusted for all in pb_hba.conf, & enable external connections 
in postgresql.conf, then restart the server to ensure the changes are activated.

You can also enable mapserver debugging & specify a log file to see what error 
is reported.
http://mapserver.org/optimization/debugging.html

You may also need to specify the unique ID column name & the SRID you are using 
in your DATA string, much like this one:
DATA "track_line from (select * from trip_track where trip_code = 'ind0701') as 
foo using unique id using srid 4326"

HTH,

   Brent Wood

--- On Tue, 2/14/12, Bistrais, Bob  wrote:

From: Bistrais, Bob 
Subject: [mapserver-users] Trouble connecting to PostGIS
To: mapserver-users@lists.osgeo.org
Date: Tuesday, February 14, 2012, 10:11 AM




 
 










First of all, let me state that I am brand new to PostGIS
and don’t know much about what I’m doing with it.  That being said,
I am having trouble displaying a PostGIS layer through MapServer. 

   

Right now I’m just experimenting with PostGIS and
simply trying to get a layer to display.  I installed the Postgre and PostGIS
1.5.  I used the shapefile loader to convert a couple shapefiles to PostGIS
data.  Looking in pgAdmin, I see the tables do indeed exist for these layers. 

   

So I built a MapServer map file.  Here is my connection
strings: 

   

DATA "the_geom from MylayerPoly" 

CONNECTION "user=postgisuser password=MyPassword!
dbname=postgistest host=localhost port=5432" 

CONNECTIONTYPE postgis 

   

But the layer does not display.  Incidentally, I’m
using GeoMoose as a front end for testing, but that shouldn’t matter
here. 

   

I did a mapserv –v at commandline, and I see the
parameter INPUT=POSTGIS. 

   

So as far as I know it should work. Can anyone offer
suggestions as to what I’m doing wrong? 

   

Thanks, 

Bob 







-Inline Attachment Follows-

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Trouble connecting to PostGIS

2012-02-13 Thread Jeff McKenna 
I'd use Brent's advice of always specifying a projection when loading 
the data into PG.  Then make sure your DATA statement uses that SRID:


  DATA "the_geom from test using SRID=4326 using unique id"

  or

  DATA "the_geom from test using SRID=-1 using unique id"

I try to document my usual steps at 
http://www.mapserver.org/input/vector/postgis.html


-jeff




--
Jeff McKenna
MapServer Consulting and Training Services
http://www.gatewaygeomatics.com/


___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

RE: [mapserver-users] Trouble connecting to PostGIS

2012-02-13 Thread Bistrais, Bob 
Thanks Brent.  The postgis user SHOULD have access to the table, as that
is the table's owner.  Coordinate system is in the map file under
Projection.

 

Here is what the debug log showed me (Metwp100Poly is the actual name of
the table, and MAINE is the layer name):

 

[Mon Feb 13 16:31:32 2012].453000 CGI Request 1 on process 1300

[Mon Feb 13 16:31:32 2012].687000 msPOSTGISLayerRetrievePGVersion():
Query error. Error executing POSTGIS statement
(msPOSTGISLayerRetrievePGVersion():select substring(version() from 12
for (position('on' in version()) - 13))

[Mon Feb 13 16:31:32 2012].687000 prepare_database(): Query error. Error
declaring cursor: ERROR:  relation "metwp100poly" does not exist

LINE 1: ...lection(force_2d(the_geom)),'NDR'),OID::text from
Metwp100Po...

 ^

 

With query string: DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from
Metwp100Poly WHERE the_geom && setSRID('BOX3D(134000
4692598.18523154,890900 5307401.81476846)'::BOX3D,
find_srid('','Metwp100Poly','the_geom') )

 

[Mon Feb 13 16:31:32 2012].687000 msDrawMap(): Image handling error.
Failed to draw layer named 'MAINE'.

[Mon Feb 13 16:31:32 2012].687000 msFreeMap(): freeing map at 015258C8.

 



From: Brent Fraser [mailto:bfra...@geoanalytic.com] 
Sent: Monday, February 13, 2012 4:25 PM
To: Bistrais, Bob
Cc: mapserver-users@lists.osgeo.org
Subject: Re: [mapserver-users] Trouble connecting to PostGIS

 

Bob,
  Typical problems are 
- the connecting user (postgisuser) does not have privilege to
access the table
- coordinate system is not specified in data (SRID=-1) and not
in the mapfile either
-  and others

Turn on the layer debug logging by adding 

DEBUG 5

to your mapfile and looking a the log.  You can also execute the SQL
statements shown in the log file using pgAdmin's SQL tool to see
additional information.
  



Best Regards,
Brent Fraser


On 2/13/2012 2:11 PM, Bistrais, Bob wrote: 

First of all, let me state that I am brand new to PostGIS and don't know
much about what I'm doing with it.  That being said, I am having trouble
displaying a PostGIS layer through MapServer.

 

Right now I'm just experimenting with PostGIS and simply trying to get a
layer to display.  I installed the Postgre and PostGIS 1.5.  I used the
shapefile loader to convert a couple shapefiles to PostGIS data.
Looking in pgAdmin, I see the tables do indeed exist for these layers.

 

So I built a MapServer map file.  Here is my connection strings:

 

DATA "the_geom from MylayerPoly"

CONNECTION "user=postgisuser password=MyPassword! dbname=postgistest
host=localhost port=5432"

CONNECTIONTYPE postgis

 

But the layer does not display.  Incidentally, I'm using GeoMoose as a
front end for testing, but that shouldn't matter here.

 

I did a mapserv -v at commandline, and I see the parameter
INPUT=POSTGIS.

 

So as far as I know it should work. Can anyone offer suggestions as to
what I'm doing wrong?

 

Thanks,

Bob






___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

Re: [mapserver-users] Trouble connecting to PostGIS

2012-02-13 Thread Brent Fraser 

Bob,
  Typical problems are
- the connecting user (postgisuser) does not have privilege to 
access the table
- coordinate system is not specified in data (SRID=-1) and not 
in the mapfile either

-  and others

Turn on the layer debug logging by adding

DEBUG 5

to your mapfile and looking a the log.  You can also execute the SQL 
statements shown in the log file using pgAdmin's SQL tool to see 
additional information.


Best Regards,
Brent Fraser


On 2/13/2012 2:11 PM, Bistrais, Bob wrote:


First of all, let me state that I am brand new to PostGIS and don't 
know much about what I'm doing with it.  That being said, I am having 
trouble displaying a PostGIS layer through MapServer.


Right now I'm just experimenting with PostGIS and simply trying to get 
a layer to display.  I installed the Postgre and PostGIS 1.5.  I used 
the shapefile loader to convert a couple shapefiles to PostGIS data.  
Looking in pgAdmin, I see the tables do indeed exist for these layers.


So I built a MapServer map file.  Here is my connection strings:

DATA "the_geom from MylayerPoly"

CONNECTION "user=postgisuser password=MyPassword! dbname=postgistest 
host=localhost port=5432"


CONNECTIONTYPE postgis

But the layer does not display.  Incidentally, I'm using GeoMoose as a 
front end for testing, but that shouldn't matter here.


I did a mapserv --v at commandline, and I see the parameter INPUT=POSTGIS.

So as far as I know it should work. Can anyone offer suggestions as to 
what I'm doing wrong?


Thanks,

Bob



___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users

[mapserver-users] Trouble connecting to PostGIS

2012-02-13 Thread Bistrais, Bob 
First of all, let me state that I am brand new to PostGIS and don't know
much about what I'm doing with it.  That being said, I am having trouble
displaying a PostGIS layer through MapServer.

 

Right now I'm just experimenting with PostGIS and simply trying to get a
layer to display.  I installed the Postgre and PostGIS 1.5.  I used the
shapefile loader to convert a couple shapefiles to PostGIS data.
Looking in pgAdmin, I see the tables do indeed exist for these layers.

 

So I built a MapServer map file.  Here is my connection strings:

 

DATA "the_geom from MylayerPoly"

CONNECTION "user=postgisuser password=MyPassword! dbname=postgistest
host=localhost port=5432"

CONNECTIONTYPE postgis

 

But the layer does not display.  Incidentally, I'm using GeoMoose as a
front end for testing, but that shouldn't matter here.

 

I did a mapserv -v at commandline, and I see the parameter
INPUT=POSTGIS.

 

So as far as I know it should work. Can anyone offer suggestions as to
what I'm doing wrong?

 

Thanks,

Bob

___
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users