[Geoserver-users] Question about SRS lookup

Richard_Matsunaga · June 26, 2007, 4:15pm

I am encountering a situation with SRS lookup that I don’t understand.

I use ogr2ogr to convert a CSV file into a PostGIS table. I use the VRT to assign a spatial reference (EPSG:4326 in this case) and the columns that make the x and y points in the CSV file.

In the DB, the the table ends up with the following constraint:

ADD CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 32773);

If I try to lookup the SRS when adding this table as a FeatureType, it fails. Setting the SRS to 4326 works and is able to generate the bounding box.

Does anyone here understand why that table with the SRID=32773 fails to lookup the SRS in GeoServer?

I can convert a (different) WGS84 MapInfo tab file to PostgreSQL table and GeoServer is happy with the lookup, and I see that the table constraint has a different SRID:

ADD CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 32767);

This is probably just my limited knowledge of projections at work here, but I’d appreciate it if anyone can shed some light.

Richard

Andrea_Aime3 · June 26, 2007, 4:19pm

Richard Matsunaga ha scritto:

I am encountering a situation with SRS lookup that I don't understand.
I use ogr2ogr to convert a CSV file into a PostGIS table. I use the VRT to assign a spatial reference (EPSG:4326 in this case) and the columns that make the x and y points in the CSV file.
In the DB, the the table ends up with the following constraint:
ADD CONSTRAINT enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 32773);
If I try to lookup the SRS when adding this table as a FeatureType, it fails. Setting the SRS to 4326 works and is able to generate the bounding box.
Does anyone here understand why that table with the SRID=32773 fails to lookup the SRS in GeoServer?

Richard, Geoserver does not inspect the actual geometries, that would
be too expensive, but uses the geometry_columns metadata instead.
Is that one consistent with the geometries?
Cheers
Andrea

Richard_Matsunaga · June 26, 2007, 6:38pm

-----Original Message-----
From: Andrea Aime [mailto:aaime@anonymised.com]

Richard Matsunaga ha scritto:
> I am encountering a situation with SRS lookup that I don't
understand.
>
> I use ogr2ogr to convert a CSV file into a PostGIS table. I use the
> VRT to assign a spatial reference (EPSG:4326 in this case) and the
> columns that make the x and y points in the CSV file.
>
> In the DB, the the table ends up with the following constraint:
>
> ADD CONSTRAINT enforce_srid_wkb_geometry CHECK
(srid(wkb_geometry)
> = 32773);
>
> If I try to lookup the SRS when adding this table as a
FeatureType, it
> fails. Setting the SRS to 4326 works and is able to generate the
> bounding box.
>
> Does anyone here understand why that table with the
SRID=32773 fails
> to lookup the SRS in GeoServer?

Richard, Geoserver does not inspect the actual geometries,
that would be too expensive, but uses the geometry_columns
metadata instead.
Is that one consistent with the geometries?
Cheers
Andrea

Thanks for that info. I didn't know about the geometry_columns table. I
don't know how that table is maintained, but it contains a lot of defunct
data. In my case it had a record for my table that contained a value of -1
for the SRID (deleting the table seems to have no effect on the
corresponding row in the geometry_columns table). After I removed that row
manually and converted with ogr2ogr again, the SRID was set to 32773, but
GeoServer still doesn't like that SRID.

Richard

Andrea_Aime3 · June 26, 2007, 7:12pm

Richard Matsunaga ha scritto:

Thanks for that info. I didn't know about the geometry_columns table. I
don't know how that table is maintained, but it contains a lot of defunct
data. In my case it had a record for my table that contained a value of -1
for the SRID (deleting the table seems to have no effect on the
corresponding row in the geometry_columns table). After I removed that row
manually and converted with ogr2ogr again, the SRID was set to 32773, but
GeoServer still doesn't like that SRID.

Hum, have you restarted Geoserver after changing the SRID? Geoserver keeps the information read from the database cached for performance reasons, but there is no way at the moment to force it refresh them

Cheers
Andrea

Crawford_Jr_Stephen · June 26, 2007, 7:25pm

As far as I know an SRS with srid=32773 is not in the Geoserver srs
database....hit the srs help from the config page. If you're really bound
to using this SRS, you will then need to add it as a user-defined or custom
SRS to both the PostGIS db and the Geoserver db....there are instructions to
do this on the Geoserver homepage somewhere.

Steve

Stephen Crawford
Center for Environmental Informatics
GeoVISTA Center
The Pennsylvania State University
814.865.9905
src176@anonymised.com

-----Original Message-----
From: geoserver-users-bounces@lists.sourceforge.net
[mailto:geoserver-users-bounces@lists.sourceforge.net] On Behalf Of Andrea
Aime
Sent: Tuesday, June 26, 2007 3:12 PM
To: Richard Matsunaga
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Question about SRS lookup

Richard Matsunaga ha scritto:

Thanks for that info. I didn't know about the geometry_columns table.
I don't know how that table is maintained, but it contains a lot of
defunct data. In my case it had a record for my table that contained a
value of -1 for the SRID (deleting the table seems to have no effect
on the corresponding row in the geometry_columns table). After I
removed that row manually and converted with ogr2ogr again, the SRID
was set to 32773, but GeoServer still doesn't like that SRID.

Hum, have you restarted Geoserver after changing the SRID? Geoserver keeps
the information read from the database cached for performance reasons, but
there is no way at the moment to force it refresh them

Cheers
Andrea

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express Download DB2 Express C - the
FREE version of DB2 express and take control of your XML. No limits. Just
data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Richard_Matsunaga · June 26, 2007, 7:55pm

Right - which is why I don't understand how setting an SRS of EPSG:4326
(which is supported by GeoServer) to the geometry in the PG table results in
an SRID which is not supported by GeoServer.

I have a MapInfo file with WGS84 that I convert to Postgres and GeoServer
happily looks up the SRS. This table also has an SRID of 32773.

Richard

-----Original Message-----
From: Stephen Crawford [mailto:src176@anonymised.com]
Sent: June 26, 2007 3:25 PM
To: 'Andrea Aime'; 'Richard Matsunaga'
Cc: geoserver-users@lists.sourceforge.net
Subject: RE: [Geoserver-users] Question about SRS lookup

As far as I know an SRS with srid=32773 is not in the
Geoserver srs database....hit the srs help from the config
page. If you're really bound to using this SRS, you will
then need to add it as a user-defined or custom SRS to both
the PostGIS db and the Geoserver db....there are instructions
to do this on the Geoserver homepage somewhere.

Steve

Stephen Crawford
Center for Environmental Informatics
GeoVISTA Center
The Pennsylvania State University
814.865.9905
src176@anonymised.com

-----Original Message-----
From: geoserver-users-bounces@lists.sourceforge.net
[mailto:geoserver-users-bounces@lists.sourceforge.net] On
Behalf Of Andrea Aime
Sent: Tuesday, June 26, 2007 3:12 PM
To: Richard Matsunaga
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Question about SRS lookup

Richard Matsunaga ha scritto:

> Thanks for that info. I didn't know about the
geometry_columns table.
> I don't know how that table is maintained, but it contains a lot of
> defunct data. In my case it had a record for my table that
contained a
> value of -1 for the SRID (deleting the table seems to have
no effect
> on the corresponding row in the geometry_columns table). After I
> removed that row manually and converted with ogr2ogr again,
the SRID
> was set to 32773, but GeoServer still doesn't like that SRID.

Hum, have you restarted Geoserver after changing the SRID?
Geoserver keeps the information read from the database cached
for performance reasons, but there is no way at the moment to
force it refresh them

Cheers
Andrea

--------------------------------------------------------------
-----------
This SF.net email is sponsored by DB2 Express Download DB2
Express C - the FREE version of DB2 express and take control
of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Crawford_Jr_Stephen · June 26, 2007, 8:20pm

So the geometry_column record for that table now has srid=32773? I've never
used ogr2ogr, but I would guess something is wrong there on defining the
output SRS. The geometry_columns table is populated by the
AddGeometryColumn function, which you should be able to see in a line of the
sql you use to make the table. If it still has the 32773 then the output
SRS is not defined correctly.

Also, when dropping a geometry table use the function
DropGeometryTable....it will also drop the record from the geometry_columns
table.

Steve

-----Original Message-----
From: geoserver-users-bounces@lists.sourceforge.net
[mailto:geoserver-users-bounces@lists.sourceforge.net] On Behalf Of Richard
Matsunaga
Sent: Tuesday, June 26, 2007 3:55 PM
To: 'Stephen Crawford'
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Question about SRS lookup

Right - which is why I don't understand how setting an SRS of EPSG:4326
(which is supported by GeoServer) to the geometry in the PG table results in
an SRID which is not supported by GeoServer.

I have a MapInfo file with WGS84 that I convert to Postgres and GeoServer
happily looks up the SRS. This table also has an SRID of 32773.

Richard

-----Original Message-----
From: Stephen Crawford [mailto:src176@anonymised.com]
Sent: June 26, 2007 3:25 PM
To: 'Andrea Aime'; 'Richard Matsunaga'
Cc: geoserver-users@lists.sourceforge.net
Subject: RE: [Geoserver-users] Question about SRS lookup

As far as I know an SRS with srid=32773 is not in the Geoserver srs
database....hit the srs help from the config page. If you're really
bound to using this SRS, you will then need to add it as a
user-defined or custom SRS to both the PostGIS db and the Geoserver
db....there are instructions to do this on the Geoserver homepage
somewhere.

Steve

Stephen Crawford
Center for Environmental Informatics
GeoVISTA Center
The Pennsylvania State University
814.865.9905
src176@anonymised.com

-----Original Message-----
From: geoserver-users-bounces@lists.sourceforge.net
[mailto:geoserver-users-bounces@lists.sourceforge.net] On Behalf Of
Andrea Aime
Sent: Tuesday, June 26, 2007 3:12 PM
To: Richard Matsunaga
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Question about SRS lookup

Richard Matsunaga ha scritto:

> Thanks for that info. I didn't know about the
geometry_columns table.
> I don't know how that table is maintained, but it contains a lot of
> defunct data. In my case it had a record for my table that
contained a
> value of -1 for the SRID (deleting the table seems to have
no effect
> on the corresponding row in the geometry_columns table). After I
> removed that row manually and converted with ogr2ogr again,
the SRID
> was set to 32773, but GeoServer still doesn't like that SRID.

Hum, have you restarted Geoserver after changing the SRID?
Geoserver keeps the information read from the database cached for
performance reasons, but there is no way at the moment to force it
refresh them

Cheers
Andrea

--------------------------------------------------------------
-----------
This SF.net email is sponsored by DB2 Express Download DB2 Express C -
the FREE version of DB2 express and take control of your XML. No
limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express Download DB2 Express C - the
FREE version of DB2 express and take control of your XML. No limits. Just
data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Richard_Matsunaga · June 26, 2007, 8:47pm

Thanks Steve, that is some great info for a newbie who was hoping to just
use these tools without any special knowledge of the tools themselves

It seems that ogr2ogr is the cause of the SRID problem.

http://lists.maptools.org/pipermail/gdal-dev/2007-February/011883.html

I guess I may have to work around this and set the SRID myself.

Cheers,
Richard

-----Original Message-----
From: Stephen Crawford [mailto:src176@anonymised.com]
Sent: June 26, 2007 4:21 PM
To: 'Richard Matsunaga'
Cc: geoserver-users@lists.sourceforge.net
Subject: RE: [Geoserver-users] Question about SRS lookup

So the geometry_column record for that table now has
srid=32773? I've never used ogr2ogr, but I would guess
something is wrong there on defining the output SRS. The
geometry_columns table is populated by the AddGeometryColumn
function, which you should be able to see in a line of the
sql you use to make the table. If it still has the 32773
then the output SRS is not defined correctly.

Also, when dropping a geometry table use the function
DropGeometryTable....it will also drop the record from the
geometry_columns table.

Steve

-----Original Message-----
From: geoserver-users-bounces@lists.sourceforge.net
[mailto:geoserver-users-bounces@lists.sourceforge.net] On
Behalf Of Richard Matsunaga
Sent: Tuesday, June 26, 2007 3:55 PM
To: 'Stephen Crawford'
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] Question about SRS lookup

Right - which is why I don't understand how setting an SRS of
EPSG:4326 (which is supported by GeoServer) to the geometry
in the PG table results in an SRID which is not supported by
GeoServer.

I have a MapInfo file with WGS84 that I convert to Postgres
and GeoServer happily looks up the SRS. This table also has
an SRID of 32773.

Richard

> -----Original Message-----
> From: Stephen Crawford [mailto:src176@anonymised.com]
> Sent: June 26, 2007 3:25 PM
> To: 'Andrea Aime'; 'Richard Matsunaga'
> Cc: geoserver-users@lists.sourceforge.net
> Subject: RE: [Geoserver-users] Question about SRS lookup
>
> As far as I know an SRS with srid=32773 is not in the Geoserver srs
> database....hit the srs help from the config page. If
you're really
> bound to using this SRS, you will then need to add it as a
> user-defined or custom SRS to both the PostGIS db and the Geoserver
> db....there are instructions to do this on the Geoserver homepage
> somewhere.
>
> Steve
>
> Stephen Crawford
> Center for Environmental Informatics
> GeoVISTA Center
> The Pennsylvania State University
> 814.865.9905
> src176@anonymised.com
>
>
>
> -----Original Message-----
> From: geoserver-users-bounces@lists.sourceforge.net
> [mailto:geoserver-users-bounces@lists.sourceforge.net] On Behalf Of
> Andrea Aime
> Sent: Tuesday, June 26, 2007 3:12 PM
> To: Richard Matsunaga
> Cc: geoserver-users@lists.sourceforge.net
> Subject: Re: [Geoserver-users] Question about SRS lookup
>
> Richard Matsunaga ha scritto:
>
> > Thanks for that info. I didn't know about the
> geometry_columns table.
> > I don't know how that table is maintained, but it
contains a lot of
> > defunct data. In my case it had a record for my table that
> contained a
> > value of -1 for the SRID (deleting the table seems to have
> no effect
> > on the corresponding row in the geometry_columns table). After I
> > removed that row manually and converted with ogr2ogr again,
> the SRID
> > was set to 32773, but GeoServer still doesn't like that SRID.
>
> Hum, have you restarted Geoserver after changing the SRID?
> Geoserver keeps the information read from the database cached for
> performance reasons, but there is no way at the moment to force it
> refresh them
>
> Cheers
> Andrea
>
> --------------------------------------------------------------
> -----------
> This SF.net email is sponsored by DB2 Express Download DB2
Express C -
> the FREE version of DB2 express and take control of your XML. No
> limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> _______________________________________________
> Geoserver-users mailing list
> Geoserver-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>

--------------------------------------------------------------
-----------
This SF.net email is sponsored by DB2 Express Download DB2
Express C - the FREE version of DB2 express and take control
of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users