[Geoserver-users] ERROR: Operation on mixed SRID geometries

I am working on a web map project with GeoServer 2.4.1, OpenLayers 2.12 and GeoExt.

I’m having some trouble with the OpenLayers.Control.WMSGetFeatureInfo. On certain layers, GeoServer returns “ERROR: Operation on mixed SRID geometries”.

The request from OpenLayers for one of the layers looks like this: http://myserver:8080/geoserver/ows?LAYERS=heartland%3Atraffic_scenario_a&QUERY_LAYERS=heartland%3Atraffic_scenario_a&STYLES=&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&BBOX=-10721313.983985%2C5029177.270038%2C-10642507.407823%2C5080619.390073&FEATURE_COUNT=3&HEIGHT=673&WIDTH=1031&FORMAT=image%2Fpng&INFO_FORMAT=application%2Fvnd.ogc.gml&X=265&Y=258

The query looks like this:

SELECT count(*) AS gt_result_ FROM (SELECT * FROM “heartland”.“traffic_scenario_a” WHERE (“geom” && ST_GeomFromText(‘POLYGON ((-10701440.356630858 5061280.821917198, -10700675.986348007 5061280.821917198, -10700675.986348007 5060516.45163436, -10701440.356630858 5060516.45163436, -10701440.356630858 5061280.821917198))’, 0) AND ST_Intersects(“geom”, ST_GeomFromText(‘POLYGON ((-10701440.356630858 5061280.821917198, -10700675.986348007 5061280.821917198, -10700675.986348007 5060516.45163436, -10701440.356630858 5060516.45163436, -10701440.356630858 5061280.821917198))’, 0)) AND ((“tot_avg__5” > 0 AND “tot_avg__5” <= 4) OR (“tot_avg__5” > 4 AND “tot_avg__5” <= 5) OR (“tot_avg__5” > 5 AND “tot_avg__5” <= 6) OR (“tot_avg__5” > 6 AND “tot_avg__5” <= 7) OR (“tot_avg__5” > 7 AND “tot_avg__5” <= 8) OR (“tot_avg__5” > 8 AND “tot_avg__5” <= 9) OR (“tot_avg__5” > 9 AND “tot_avg__5” <= 10) OR (“tot_avg__5” > 10 AND “tot_avg__5” <= 20))) LIMIT 3) gt_limited_

As you can see, the SRID in the ST_GeomFromText functions is 0. When I manually change the SRID in the query to 3857 (which is the SRID of the layer), I can run the query successfully. The entry in geometry_columns is correct (3857). It also matches the geom column in my traffic_scenario_a table.

How can I fix this so that GeoServer uses the correct SRID in this query?

Bettina Lechner

blechner@…6560…

On Tue, Jun 3, 2014 at 6:18 PM, Bettina Lechner <blechner@anonymised.com>
wrote:

I am working on a web map project with GeoServer 2.4.1, OpenLayers 2.12
and GeoExt.

I’m having some trouble with the OpenLayers.Control.WMSGetFeatureInfo. On
certain layers, GeoServer returns “ERROR: Operation on mixed SRID
geometries”.

The request from OpenLayers for one of the layers looks like this:
http://myserver:8080/geoserver/ows?LAYERS=heartland%3Atraffic_scenario_a&QUERY_LAYERS=heartland%3Atraffic_scenario_a&STYLES=&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&BBOX=-10721313.983985%2C5029177.270038%2C-10642507.407823%2C5080619.390073&FEATURE_COUNT=3&HEIGHT=673&WIDTH=1031&FORMAT=image%2Fpng&INFO_FORMAT=application%2Fvnd.ogc.gml&X=265&Y=258

The query looks like this:

SELECT count(*) AS gt_result_ FROM (SELECT * FROM
"heartland"."traffic_scenario_a" WHERE ("geom" && ST_GeomFromText('POLYGON
((-10701440.356630858 5061280.821917198, -10700675.986348007
5061280.821917198, -10700675.986348007 5060516.45163436,
-10701440.356630858 5060516.45163436, -10701440.356630858
5061280.821917198))', 0) AND ST_Intersects("geom", ST_GeomFromText('POLYGON
((-10701440.356630858 5061280.821917198, -10700675.986348007
5061280.821917198, -10700675.986348007 5060516.45163436,
-10701440.356630858 5060516.45163436, -10701440.356630858
5061280.821917198))', 0)) AND (("tot_avg__5" > 0 AND "tot_avg__5" <= 4) OR
("tot_avg__5" > 4 AND "tot_avg__5" <= 5) OR ("tot_avg__5" > 5 AND
"tot_avg__5" <= 6) OR ("tot_avg__5" > 6 AND "tot_avg__5" <= 7) OR
("tot_avg__5" > 7 AND "tot_avg__5" <= 8) OR ("tot_avg__5" > 8 AND
"tot_avg__5" <= 9) OR ("tot_avg__5" > 9 AND "tot_avg__5" <= 10) OR
("tot_avg__5" > 10 AND "tot_avg__5" <= 20))) LIMIT 3) gt_limited_

As you can see, the SRID in the ST_GeomFromText functions is 0. When I
manually change the SRID in the query to 3857 (which is the SRID of the
layer), I can run the query successfully. The entry in geometry_columns is
correct (3857). It also matches the geom column in my traffic_scenario_a
table.

How can I fix this so that GeoServer uses the correct SRID in this query?

That is a PostGIS backend, isn't it? What is the geometry_columns saying
about that table/column?
Also, any possibility you might have even just one geometry in the table
with srid=0?

Cheers
Andrea

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

Yes, it is PostGIS. Sorry, I forgot to mention that.

Here’s the geometry_columns content for that table:

···


f_table_catalog



f_table_schema



f_table_name



f_geometry_column



coord_dimension



srid



type



heartland



heartland



traffic_scenario_a



geom



2



3857



MULTIPOLYGON

Running

SELECT DISTINCT(ST_SRID(ST_AsEWKT(geom))) FROM heartland.traffic_scenario_a;

Returns only one row containing 3857.

Thank you!

Bettina

(402) 554-2088

From: andrea.aime@…84… [mailto:andrea.aime@…84…] On Behalf Of Andrea Aime
Sent: Tuesday, June 03, 2014 11:36
To: Bettina Lechner
Cc: geoserver-users@lists.sourceforge.net
Subject: Re: [Geoserver-users] ERROR: Operation on mixed SRID geometries

On Tue, Jun 3, 2014 at 6:18 PM, Bettina Lechner <blechner@…6560…> wrote:

I am working on a web map project with GeoServer 2.4.1, OpenLayers 2.12 and GeoExt.

I’m having some trouble with the OpenLayers.Control.WMSGetFeatureInfo. On certain layers, GeoServer returns “ERROR: Operation on mixed SRID geometries”.

The request from OpenLayers for one of the layers looks like this: http://myserver:8080/geoserver/ows?LAYERS=heartland%3Atraffic_scenario_a&QUERY_LAYERS=heartland%3Atraffic_scenario_a&STYLES=&SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&BBOX=-10721313.983985%2C5029177.270038%2C-10642507.407823%2C5080619.390073&FEATURE_COUNT=3&HEIGHT=673&WIDTH=1031&FORMAT=image%2Fpng&INFO_FORMAT=application%2Fvnd.ogc.gml&X=265&Y=258

The query looks like this:

SELECT count(*) AS gt_result_ FROM (SELECT * FROM “heartland”.“traffic_scenario_a” WHERE (“geom” && ST_GeomFromText(‘POLYGON ((-10701440.356630858 5061280.821917198, -10700675.986348007 5061280.821917198, -10700675.986348007 5060516.45163436, -10701440.356630858 5060516.45163436, -10701440.356630858 5061280.821917198))’, 0) AND ST_Intersects(“geom”, ST_GeomFromText(‘POLYGON ((-10701440.356630858 5061280.821917198, -10700675.986348007 5061280.821917198, -10700675.986348007 5060516.45163436, -10701440.356630858 5060516.45163436, -10701440.356630858 5061280.821917198))’, 0)) AND ((“tot_avg__5” > 0 AND “tot_avg__5” <= 4) OR (“tot_avg__5” > 4 AND “tot_avg__5” <= 5) OR (“tot_avg__5” > 5 AND “tot_avg__5” <= 6) OR (“tot_avg__5” > 6 AND “tot_avg__5” <= 7) OR (“tot_avg__5” > 7 AND “tot_avg__5” <= 8) OR (“tot_avg__5” > 8 AND “tot_avg__5” <= 9) OR (“tot_avg__5” > 9 AND “tot_avg__5” <= 10) OR (“tot_avg__5” > 10 AND “tot_avg__5” <= 20))) LIMIT 3) gt_limited_

As you can see, the SRID in the ST_GeomFromText functions is 0. When I manually change the SRID in the query to 3857 (which is the SRID of the layer), I can run the query successfully. The entry in geometry_columns is correct (3857). It also matches the geom column in my traffic_scenario_a table.

How can I fix this so that GeoServer uses the correct SRID in this query?

That is a PostGIS backend, isn’t it? What is the geometry_columns saying about that table/column?

Also, any possibility you might have even just one geometry in the table with srid=0?

Cheers

Andrea

==

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK

for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it


On Tue, Jun 3, 2014 at 6:44 PM, Bettina Lechner <blechner@anonymised.com>
wrote:

Yes, it is PostGIS. Sorry, I forgot to mention that.

Here’s the geometry_columns content for that table:

f_table_catalog

f_table_schema

f_table_name

f_geometry_column

coord_dimension

srid

type

heartland

heartland

traffic_scenario_a

geom

2

3857

MULTIPOLYGON

Running

SELECT DISTINCT(ST_SRID(ST_AsEWKT(geom))) FROM
heartland.traffic_scenario_a;

Returns only one row containing 3857.

Hmm... wondering if it's the GeoServer version, it's pretty old now. Any
chance you can upgrade to 2.5.1?

Cheers
Andrea

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------

I will try that, yes.

On further reflection, I thought of something else that is possibly related:

I use shp2pgsql to import Esri shapefiles into PostGIS. For some reason, when the table is created, the SRID is set to 0. Then I change it to the appropriate one in PostGIS. When I then add it to GeoServer, it doesn’t pick up on that and leaves the “Native SRS” field blank, so I have to use the Declared SRS instead.

Bettina

(402) 554-2088

···

On Tue, Jun 3, 2014 at 6:44 PM, Bettina Lechner <blechner@…6560…> wrote:

Yes, it is PostGIS. Sorry, I forgot to mention that.

Here’s the geometry_columns content for that table:



f_table_catalog



f_table_schema



f_table_name



f_geometry_column



coord_dimension



srid



type



heartland



heartland



traffic_scenario_a



geom



2



3857



MULTIPOLYGON

Running

SELECT DISTINCT(ST_SRID(ST_AsEWKT(geom))) FROM heartland.traffic_scenario_a;

Returns only one row containing 3857.

Hmm… wondering if it’s the GeoServer version, it’s pretty old now. Any chance you can upgrade to 2.5.1?

Cheers

Andrea

==

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK

for more information.

==

Ing. Andrea Aime

@geowolf

Technical Lead

GeoSolutions S.A.S.

Via Poggio alle Viti 1187

55054 Massarosa (LU)

Italy

phone: +39 0584 962313

fax: +39 0584 1660272

mob: +39 339 8844549

http://www.geo-solutions.it

http://twitter.com/geosolutions_it


On Tue, Jun 3, 2014 at 7:50 PM, Bettina Lechner <blechner@anonymised.com>
wrote:

I will try that, yes.

On further reflection, I thought of something else that is possibly
related:

I use shp2pgsql to import Esri shapefiles into PostGIS. For some reason,
when the table is created, the SRID is set to 0. Then I change it to the
appropriate one in PostGIS. When I then add it to GeoServer, it doesn’t
pick up on that and leaves the “Native SRS” field blank, so I have to use
the Declared SRS instead.

When using shp2pgsql it's a good practice to pass the -s parameter (and -I
too) while importing, e.g., if your shapefile
is in WGS84 you'll use:

shp2pgsql -s 4326 -I myshapefile.shp mytable

Cheers
Andrea

--

Meet us at GEO Business 2014! in London! Visit http://goo.gl/fES3aK
for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

-------------------------------------------------------