[Geoserver-users] Filter by ID list

GeoServer GeoServer User
1

Hello

I am just starting to use filtering for WMS images. I wish to filter roads based on a list of IDs. The SQL equivalent I want to do is “WHERE link_id IN (5500, 6600, 1111, 2154)”. Such syntax doesn’t work in CQL. They have some functions in2, in3, up till in10, but these are open-ended lists that can count in the thousands.

Are there some special syntax for CQL for this? Does OGC filters support it? I see there is a FID-filter that takes a list of comma-separated IDs. Is it possible to make Feature ID use the primary key of the data source table instead of some pseudorandom sequence that is completely useless for everything? The road network is stored in a PostGIS database.

Before anyone asks, this is needed for a route calculator. Our calculator… calculates… the route and returns the path as a sequence of road IDs. So naturally long routes have a lot of roads, and every route is different.

I know that if I absolutely have to, I can reshape the query to “link_id = 5500 OR link_id = 6600 OR link_id = 1111 OR …”, but I would rather not. It is verbose, and I already have the list comma-separated.

Kind regards

Ugland IT Group AS

Thomas Jahnsen

Developer

Tel: + 47 67 59 28 23

E-mail: thja@anonymised.com

UGLAND IT GROUP AS, Strandveien 13, Pb. 58, 1324 Lysaker, org.nr. 971 058 773

Tlf: +47 67 59 28 10, fax: +47 67 59 28 11, email: firmapost@anonymised.com, internet: www.uglandit.com

2

Thomas Jahnsen ha scritto:

Hello

Hi. Wow, many questions

I am just starting to use filtering for WMS images. I wish to filter roads based on a list of IDs. The SQL equivalent I want to do is “WHERE link_id IN (5500, 6600, 1111, 2154)”. Such syntax doesn’t work in CQL. They have some functions in2, in3, up till in10, but these are open-ended lists that can count in the thousands.

No, OGC never defined a id filter for CQL. In GeoServer 2.0
we'll have our own extension of the base CQL sytanx, named
ECQL (Extended-CQL) that will support fid filters too.
Anyways, if you count in the thousands, you're out of luck, you'll
soon end up with a url too long for your browser/http server to handle.

Are there some special syntax for CQL for this? Does OGC filters support it? I see there is a FID-filter that takes a list of comma-separated IDs. Is it possible to make Feature ID use the primary key of the data source table instead of some pseudorandom sequence that is completely useless for everything? The road network is stored in a PostGIS database.

If you have a primary key, it will be used to generate the FIDs.
If you have random values, then the most likely cause it's that you
don't have a primary key on your table. Or the primary key has some
odd structure that we cannot handle, but I'm not aware of such a case.
Another case in which you get randomly generated fids is you using
a view. Unfortunately we don't have a way to configure GeoServer
so that it picks up the FID from a user defined field returned
by a view.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

3

Hi

Well, it is really only one question, with various alternatives.
Well, I got the FID to work - the data was loaded with shp2pgsql, which adds its own primary key. I usually work with the street data in Oracle, where the primary key is link_id. I changed that, and the FID got a little better. But still, is it possible to skip the layer name prefix? Now it is like "navteq_streets_polyline.571393864".

I plan to overcome URL length problems by using POST. I can still use normal HTTP headers for POST, right? I don't have to use WMS XML query?

Kind regards
Ugland IT Group AS

Thomas Jahnsen
Developer
E-mail: thja@anonymised.com

Hi. Wow, many questions

> I am just starting to use filtering for WMS images. I wish to filter
> roads based on a list of IDs. The SQL equivalent I want to do is
"WHERE
> link_id IN (5500, 6600, 1111, 2154)". Such syntax doesn't work in
CQL.
> They have some functions in2, in3, up till in10, but these are
> open-ended lists that can count in the thousands.

No, OGC never defined a id filter for CQL. In GeoServer 2.0
we'll have our own extension of the base CQL sytanx, named
ECQL (Extended-CQL) that will support fid filters too.
Anyways, if you count in the thousands, you're out of luck, you'll
soon end up with a url too long for your browser/http server to handle.

> Are there some special syntax for CQL for this? Does OGC filters
support
> it? I see there is a FID-filter that takes a list of comma-separated
> IDs. Is it possible to make Feature ID use the primary key of the
data
> source table instead of some pseudorandom sequence that is completely
> useless for everything? The road network is stored in a PostGIS
database.

If you have a primary key, it will be used to generate the FIDs.
If you have random values, then the most likely cause it's that you
don't have a primary key on your table. Or the primary key has some
odd structure that we cannot handle, but I'm not aware of such a case.
Another case in which you get randomly generated fids is you using
a view. Unfortunately we don't have a way to configure GeoServer
so that it picks up the FID from a user defined field returned
by a view.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

4

Thomas Jahnsen ha scritto:

Hi

Well, it is really only one question, with various alternatives. Well, I got the FID to work - the data was loaded with shp2pgsql,
which adds its own primary key. I usually work with the street data
in Oracle, where the primary key is link_id. I changed that, and the
FID got a little better. But still, is it possible to skip the layer
name prefix? Now it is like "navteq_streets_polyline.571393864".

Not possible. By WFS spec we have to be able to answer a request like:

http://host/geoserver/wfs?REQUEST=WFS&...&FID=navteq_streets_polyline.571393864

that is, a GetFeature without a typeName. Each feature should be
uniquely identified within the server. To satisfy that request
fully the FID should contain the namespace too (as you might have
two equally named layers in two different namespaces) but so far we
don't do that to avoid excruciating long names.

You can set the alias in your layers and just call that "strees",
the "alias" will in fact become the new name of the layer.

I plan to overcome URL length problems by using POST. I can still use
normal HTTP headers for POST, right? I don't have to use WMS XML
query?

By standard you have to use a XML query. Practically speaking, and
just for GeoServer, I believe you can post your request as if it
was a form, and it will be handled as a GET request.
But I'm not sure, as this is not an official "feature" or GeoServer.
Justin wrote the dispatcher and can provide more input
on this one.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

5

By standard you have to use a XML query. Practically speaking, and
just for GeoServer, I believe you can post your request as if it
was a form, and it will be handled as a GET request.
But I'm not sure, as this is not an official "feature" or GeoServer.
Justin wrote the dispatcher and can provide more input
on this one.

As long as the Content-type header is set properly (application/x-www-form-urlencoded), posting key value pairs should work just like specifying them in the query string of a get request. Also, if i remember properly, this is something tested by cite... i could be wrong through.

-Justin

Cheers
Andrea

--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

6

Hi,

With Oracle you can control FID generation pretty well by creating a view and then primary key for the view. Geoserver will use that primary key for FIDs and there is no need to change primary key of the original table. Be carefull when creating the primary key for a view, Oracle cannot know if it is unique and that may leed to dublicated FIDs and troubles with WFS clients.

-Jukka Rahkonen-

-----Alkuperäinen viesti-----
Lähettäjä: Thomas Jahnsen [mailto:thja@anonymised.com]
Lähetetty: ti 17.2.2009 16:02
Vastaanottaja: 'Andrea Aime'
Kopio: 'geoserver-users@lists.sourceforge.net'
Aihe: Re: [Geoserver-users] Filter by ID list

Hi

Well, it is really only one question, with various alternatives.
Well, I got the FID to work - the data was loaded with shp2pgsql, which adds its own primary key. I usually work with the street data in Oracle, where the primary key is link_id. I changed that, and the FID got a little better. But still, is it possible to skip the layer name prefix? Now it is like "navteq_streets_polyline.571393864".

I plan to overcome URL length problems by using POST. I can still use normal HTTP headers for POST, right? I don't have to use WMS XML query?

Kind regards
Ugland IT Group AS

Thomas Jahnsen
Developer
E-mail: thja@anonymised.com

Hi. Wow, many questions

> I am just starting to use filtering for WMS images. I wish to filter
> roads based on a list of IDs. The SQL equivalent I want to do is
"WHERE
> link_id IN (5500, 6600, 1111, 2154)". Such syntax doesn't work in
CQL.
> They have some functions in2, in3, up till in10, but these are
> open-ended lists that can count in the thousands.

No, OGC never defined a id filter for CQL. In GeoServer 2.0
we'll have our own extension of the base CQL sytanx, named
ECQL (Extended-CQL) that will support fid filters too.
Anyways, if you count in the thousands, you're out of luck, you'll
soon end up with a url too long for your browser/http server to handle.

> Are there some special syntax for CQL for this? Does OGC filters
support
> it? I see there is a FID-filter that takes a list of comma-separated
> IDs. Is it possible to make Feature ID use the primary key of the
data
> source table instead of some pseudorandom sequence that is completely
> useless for everything? The road network is stored in a PostGIS
database.

If you have a primary key, it will be used to generate the FIDs.
If you have random values, then the most likely cause it's that you
don't have a primary key on your table. Or the primary key has some
odd structure that we cannot handle, but I'm not aware of such a case.
Another case in which you get randomly generated fids is you using
a view. Unfortunately we don't have a way to configure GeoServer
so that it picks up the FID from a user defined field returned
by a view.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users