[Geoserver-users] SQL select statements to generate query layers

Sim_Kim_Sia · February 7, 2007, 9:10am

Hi,

I am currently generating a layer with moving objects using data from a SQL table in Postgresql.

Is it possible to do the same using a resultset that is generated from a SQL select statement?

Thank you

Regards
Kim Sia

Extension: 3160

[This e-mail is confidential and may be priviledged. If you are not the
intended recipient, please kindly notify us immediately and delete the message
from your system; please do not copy or use it for any purpose, nor disclose
its contents to any other person. Thank you.]
---ST Electronics Group---

Andrea_Aime3 · February 7, 2007, 9:36am

Sim Kim Sia ha scritto:

Hi,
I am currently generating a layer with moving objects using data from a SQL table in Postgresql.
Is it possible to do the same using a resultset that is generated from a SQL select statement?

Unfortunately not, out JDBC data store do not expose any way to run a direct SQL query. You're stuck with two possibilities:
* encoding your query as an OGC filter, if the query does not have joins
* prepare views that do the joins you need, and register the views as
feature types (you have to create the view, and then register its
geometric columns into the geometry_columns table).

Cheers
Andrea

Sim_Kim_Sia · February 7, 2007, 9:42am

Hi Andrea,

May I ask if my query is a

Select * from table_1, table_2 where table_1.a = table_2.a

would that work?

Is there a tutorial web page that explains how to encode a query as an OGC filter?

Thanks
how about
Regards
Kim Sia

Extension: 3160
----- Original Message ----- From: "Andrea Aime" <aaime@anonymised.com>
To: "Sim Kim Sia" <simksi@anonymised.com>
Cc: <geoserver-users@lists.sourceforge.net>
Sent: Wednesday, February 07, 2007 5:36 PM
Subject: Re: [Geoserver-users] SQL select statements to generate query layers

Sim Kim Sia ha scritto:

Hi,
I am currently generating a layer with moving objects using data from a SQL table in Postgresql.
Is it possible to do the same using a resultset that is generated from a SQL select statement?

Unfortunately not, out JDBC data store do not expose any way to run a direct SQL query. You're stuck with two possibilities:
* encoding your query as an OGC filter, if the query does not have joins
* prepare views that do the joins you need, and register the views as
feature types (you have to create the view, and then register its
geometric columns into the geometry_columns table).

Cheers
Andrea

[This e-mail is confidential and may be priviledged. If you are not the
intended recipient, please kindly notify us immediately and delete the message
from your system; please do not copy or use it for any purpose, nor disclose
its contents to any other person. Thank you.]
---ST Electronics Group---

Andrea_Aime3 · February 7, 2007, 9:55am

Sim Kim Sia ha scritto:

Hi Andrea,

May I ask if my query is a

Select * from table_1, table_2 where table_1.a = table_2.a

would that work?

Is there a tutorial web page that explains how to encode a query as an OGC filter?

I did write:
>> * encoding your query as an OGC filter, if the query does not
>> have joins

The above is a join
The only way for you is to:
1) define a view that joins the tables:
    create view MyJoinedView as
    Select * from table_1, table_2 where table_1.a = table_2.a
2) if table1.geom is a geometric column do an
    insert into geometry_columns(...)
    using the same parameters as for table1, but stating the
    column is in MyJoinedView
3) in Geoserver register MyJoinedView as a feature type (you
    may have to restart geoserver to have it notice your new view).

The OGC filters can then be used to add dynamic filtering conditions
such as show me only the features that have attributeA = 10 and things
like this (but no joins). If your filtering conditions are static, then
you don't need to use filter support at all, just add them in the
view definition, such as:

create view MyJoinedView as
Select * from table_1, table_2 where table_1.a = table_2.a
where table1.att1 = xxx and table2.att2 = yyy

Unfortunately we dont' have a tutorial on how to write OGC filters
afaik...

Cheers
Andrea

Sim_Kim_Sia · February 8, 2007, 6:22am

Hi Andrea,

I need to use the OGC filters for data from a single table.

Can you be more specific in your examples on using the OGC filters?

Thank you.

Regards
Kim Sia

Extension: 3160
----- Original Message ----- From: "Andrea Aime" <aaime@anonymised.com>
To: "Sim Kim Sia" <simksi@anonymised.com>
Cc: <geoserver-users@lists.sourceforge.net>
Sent: Wednesday, February 07, 2007 5:55 PM
Subject: Re: [Geoserver-users] SQL select statements to generate query layers

Sim Kim Sia ha scritto:

Hi Andrea,

May I ask if my query is a

Select * from table_1, table_2 where table_1.a = table_2.a

would that work?

Is there a tutorial web page that explains how to encode a query as an OGC filter?

I did write:
>> * encoding your query as an OGC filter, if the query does not
>> have joins

The above is a join
The only way for you is to:
1) define a view that joins the tables:
   create view MyJoinedView as
   Select * from table_1, table_2 where table_1.a = table_2.a
2) if table1.geom is a geometric column do an
   insert into geometry_columns(...)
   using the same parameters as for table1, but stating the
   column is in MyJoinedView
3) in Geoserver register MyJoinedView as a feature type (you
   may have to restart geoserver to have it notice your new view).

The OGC filters can then be used to add dynamic filtering conditions
such as show me only the features that have attributeA = 10 and things
like this (but no joins). If your filtering conditions are static, then
you don't need to use filter support at all, just add them in the
view definition, such as:

create view MyJoinedView as
Select * from table_1, table_2 where table_1.a = table_2.a
where table1.att1 = xxx and table2.att2 = yyy

Unfortunately we dont' have a tutorial on how to write OGC filters
afaik...

Cheers
Andrea

[This e-mail is confidential and may be priviledged. If you are not the
intended recipient, please kindly notify us immediately and delete the message
from your system; please do not copy or use it for any purpose, nor disclose
its contents to any other person. Thank you.]
---ST Electronics Group---

Andrea_Aime3 · February 8, 2007, 7:58am

Sim Kim Sia ha scritto:

Hi Andrea,

I need to use the OGC filters for data from a single table.

Can you be more specific in your examples on using the OGC filters?

Ok. The OGC Filter spec is a complex XML spec that allows to write
a "WHERE" like condition against a feature type.
You can find the full spec here: http://www.opengeospatial.org/standards/filter (there are lots of examples at the end of the paper).
Filter is used primarily by SLD (the map styling spec) and by
WFS, thought we have an initial support in WMS too.

You can find examples of SLD using OGC filters here:
http://docs.codehaus.org/display/GEOSDOC/SLD+Intro+Tutorial
http://docs.codehaus.org/display/GEOSDOC/SLD+Snippets

You can also find example OGC filters in most WFS sample requests
in the Geoserver web interface Demo section.

For using filters with WMS you have two possibilities:
* provide a full SLD, which includes both a filter and a styling,
   in the SLD_BODY parameter. You can find a sample of this approac
   among the sample requests, in the demo section (the sld looks funny
   because it has been url-encoded, no < or > can appear in a GET request
* provide just a filter using the FILTER=xxx parameter, where xxx is
   the url-encoded version of your OGC filter. If you need to specify
   more than one filter, use comma as the separator, thought this will
   change in 1.4.1 and 1.5.0, where we'll revert back to using ( ).
   This one has been added recently and is not very well tested,
   afaik due to a bug 1.4.0 allowed to specify just one filter, whilst
   1.5.0beta2 allows multiple filters separated by comma.

Hope this helps
Cheers
Andrea

Andrea_Aime3 · February 8, 2007, 8:29am

Sim Kim Sia ha scritto:

Hi Andrea,

thanks for your help.

From what I see,

the OGC filter requires me to specify a fixed value for the <literal> tag.

eg
     <PropertyIsEqualTo>

        <PropertyName>Person/Address/City</PropertyName>

        <Literal>Toronto</Literal>

     </PropertyIsEqualTo>

In the example above, I need to specify Toronto in advance.

Is there a way for OGC get a value that is determined by a javascript variable?

You can build the filter client side in javascript by string concatenation, so yes, you can do that. Unfortunately, I never coded in
javascript, so I can't provide you a sample. Keep the mails on the users mailing list, maybe someone else is able to help you.

Oh, btw, you can't use something like "Person/Address/City" in
Geoserver, since we don't support complex features (feature with nested
attributes). If you have a City attribute in the feature type you have to use <PropertyName>City</PropertyName>.

Cheers
Andrea

Sim_Kim_Sia · February 8, 2007, 8:58am

> provide just a filter using the FILTER=xxx parameter, where xxx is the url-encoded version of your OGC filter

I know about placing filter tags inside a sld file.

But since I have to generate string-concatenated filter using java script. I suppose I have to provide a filter using the Filter=xxx parameter

I suppose you are talking about something like this:

var url = ‘http://localhost:8080/geoserver/wms?filter=xxx’

yes?

Regards
Kim Sia

Extension: 3160
----- Original Message -----
From: “Andrea Aime” <aaime@anonymised.com>
To: “Sim Kim Sia” <simksi@anonymised.com>
Cc: <geoserver-users@lists.sourceforge.net>
Sent: Thursday, February 08, 2007 3:58 PM
Subject: Re: [Geoserver-users] SQL select statements to generate query layers

Sim Kim Sia ha scritto:

Hi Andrea,

I need to use the OGC filters for data from a single table.

Can you be more specific in your examples on using the OGC filters?

Ok. The OGC Filter spec is a complex XML spec that allows to write
a “WHERE” like condition against a feature type.
You can find the full spec here:
http://www.opengeospatial.org/standards/filter (there are lots of
examples at the end of the paper).
Filter is used primarily by SLD (the map styling spec) and by
WFS, thought we have an initial support in WMS too.

You can find examples of SLD using OGC filters here:
http://docs.codehaus.org/display/GEOSDOC/SLD+Intro+Tutorial
http://docs.codehaus.org/display/GEOSDOC/SLD+Snippets

You can also find example OGC filters in most WFS sample requests
in the Geoserver web interface Demo section.

For using filters with WMS you have two possibilities:

provide a full SLD, which includes both a filter and a styling,
in the SLD_BODY parameter. You can find a sample of this approac
among the sample requests, in the demo section (the sld looks funny
because it has been url-encoded, no < or > can appear in a GET request

provide just a filter using the FILTER=xxx parameter, where xxx is
the url-encoded version of your OGC filter. If you need to specify
more than one filter, use comma as the separator, thought this will
change in 1.4.1 and 1.5.0, where we’ll revert back to using ( ).
This one has been added recently and is not very well tested,
afaik due to a bug 1.4.0 allowed to specify just one filter, whilst
1.5.0beta2 allows multiple filters separated by comma.

Hope this helps
Cheers
Andrea

[This e-mail is confidential and may be priviledged. If you are not the
intended recipient, please kindly notify us immediately and delete the message
from your system; please do not copy or use it for any purpose, nor disclose
its contents to any other person. Thank you.]
---ST Electronics Group---

Andrea_Aime3 · February 8, 2007, 9:24am

Sim Kim Sia ha scritto:

/> provide just a filter using the FILTER=xxx parameter, where xxx is the url-encoded version of your OGC filter/
I know about placing filter tags inside a sld file.
But since I have to generate string-concatenated filter using java script. I suppose I have to provide a filter using the Filter=xxx parameter

Not necessarily, you can provide a full SLD document in your request using the SLD_BODY parameter. The Geoserver sample requests do contain
an example.

I suppose you are talking about something like this:

var url = 'http://localhost:8080/geoserver/wms?filter=xxx’

yes?

More or less, you have to add filter=xxx to your standard WMS request
(and you'll have to provide a filter for each layer, like you have to
provide a style for each one, separating them with commas, and adding
an empty one if a layer does not need to be filtered). As I told
you, this will work only in Geoserver 1.5.0beta2 onwards, 1.4.0 is
only able to support a single filter in a single layer request...

Cheers
Andrea

Brent_Owens1 · February 8, 2007, 4:13pm

Hi Kim,

For your <PropertyIsEqualTo> filter, you will need several <and> tags wrapped around it to capture both person, city, and address.

In javascript you can just create a string and interject variables into it like so:

var person = "Joe"
var address = "123 Fake St."
var city = "Victoria"

var filter = " <ogc:Filter>"+
            "<ogc:And>"+
            "<ogc:PropertyIsEqualTo>"+
                        "<ogc:PropertyName>Person</ogc:PropertyName>"+
                        "<ogc:Literal>"+person+"</ogc:Literal>"+
                    "</ogc:PropertyIsEqualTo>"+
            "</ogc:And>"+
            "<ogc:PropertyIsEqualTo>"+
                        "<ogc:PropertyName>Address</ogc:PropertyName>"+ <cid:part1.04050203.07070904@anonymised.com>
                        "<ogc:Literal>"+address+"</ogc:Literal>"+
                    "</ogc:PropertyIsEqualTo>"+
            "</ogc:And>"+
            "<ogc:PropertyIsEqualTo>"+
                        "<ogc:PropertyName>City</ogc:PropertyName>"+
                        "<ogc:Literal>"+city+"</ogc:Literal>"+
                    "</ogc:PropertyIsEqualTo>"+
            "</ogc:And>"+
        "</ogc:Filter>";

Then send that filter along with your request.

Hope that helps,

Brent Owens
(The Open Planning Project)

Andrea Aime wrote:

Sim Kim Sia ha scritto:


Hi Andrea,

thanks for your help.

From what I see,

the OGC filter requires me to specify a fixed value for the <literal> tag.

eg
     <PropertyIsEqualTo>

        <PropertyName>Person/Address/City</PropertyName>

        <Literal>Toronto</Literal>

     </PropertyIsEqualTo>

In the example above, I need to specify Toronto in advance.

Is there a way for OGC get a value that is determined by a javascript variable?

You can build the filter client side in javascript by string concatenation, so yes, you can do that. Unfortunately, I never coded in
javascript, so I can't provide you a sample. Keep the mails on the users mailing list, maybe someone else is able to help you.

Oh, btw, you can't use something like "Person/Address/City" in
Geoserver, since we don't support complex features (feature with nested
attributes). If you have a City attribute in the feature type you have to use <PropertyName>City</PropertyName>.

Cheers
Andrea

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Sim_Kim_Sia · February 9, 2007, 12:29am

Hi Brent,

thanks for your reply.

I saw the following in a webpage http://docs.codehaus.org/display/GEOSDOC/1+How+a+GetFeature+Request+Works

filter=<ogc:Filter
xmlns:ogc="http://ogc.org" xmlns:gml="http://www.opengis.net/gml">
<ogc:BBOX>
<ogc:PropertyName>the_geom</ogc:PropertyName>
<gml:Box srsName="http://www.opengis.net/gml/srs/epsg.xml">
<gml:coordinates>-73.99312376470733,40.76203427979042 -73.9239210030026,40.80129519821393</gml:coordinates>
</gml:Box>
</ogc:BBOX>
</ogc:Filter>Granted that the example shown was using a wfs, instead of a wms.Should I include the Filter xmlns:ogc tags as well?Thank you.Regards
Kim Sia

Extension: 3160
----- Original Message ----- From: "Brent Owens" <brentowens@anonymised.com>
To: "Sim Kim Sia" <simksi@anonymised.com>
Cc: "Andrea Aime" <aaime@anonymised.com>; "geoserver users" <geoserver-users@lists.sourceforge.net>
Sent: Friday, February 09, 2007 12:13 AM
Subject: Re: [Geoserver-users] SQL select statements to generate query layers

Hi Kim,

For your <PropertyIsEqualTo> filter, you will need several <and> tags wrapped around it to capture both person, city, and address.

In javascript you can just create a string and interject variables into it like so:

var person = "Joe"
var address = "123 Fake St."
var city = "Victoria"

var filter = " <ogc:Filter>"+
           "<ogc:And>"+
           "<ogc:PropertyIsEqualTo>"+
                       "<ogc:PropertyName>Person</ogc:PropertyName>"+
                       "<ogc:Literal>"+person+"</ogc:Literal>"+
                   "</ogc:PropertyIsEqualTo>"+
           "</ogc:And>"+
           "<ogc:PropertyIsEqualTo>"+
                       "<ogc:PropertyName>Address</ogc:PropertyName>"+ <cid:part1.04050203.07070904@anonymised.com>
                       "<ogc:Literal>"+address+"</ogc:Literal>"+
                   "</ogc:PropertyIsEqualTo>"+
           "</ogc:And>"+
           "<ogc:PropertyIsEqualTo>"+
                       "<ogc:PropertyName>City</ogc:PropertyName>"+
                       "<ogc:Literal>"+city+"</ogc:Literal>"+
                   "</ogc:PropertyIsEqualTo>"+
           "</ogc:And>"+
       "</ogc:Filter>";

Then send that filter along with your request.

Hope that helps,

Brent Owens
(The Open Planning Project)

Andrea Aime wrote:

Sim Kim Sia ha scritto:

Hi Andrea,

thanks for your help.

From what I see,

the OGC filter requires me to specify a fixed value for the <literal> tag.

eg
     <PropertyIsEqualTo>

        <PropertyName>Person/Address/City</PropertyName>

        <Literal>Toronto</Literal>

     </PropertyIsEqualTo>

In the example above, I need to specify Toronto in advance.

Is there a way for OGC get a value that is determined by a javascript variable?

You can build the filter client side in javascript by string concatenation, so yes, you can do that. Unfortunately, I never coded in
javascript, so I can't provide you a sample. Keep the mails on the users mailing list, maybe someone else is able to help you.

Oh, btw, you can't use something like "Person/Address/City" in
Geoserver, since we don't support complex features (feature with nested
attributes). If you have a City attribute in the feature type you have to use <PropertyName>City</PropertyName>.

Cheers
Andrea

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

[This e-mail is confidential and may be priviledged. If you are not the
intended recipient, please kindly notify us immediately and delete the message
from your system; please do not copy or use it for any purpose, nor disclose
its contents to any other person. Thank you.]
---ST Electronics Group---

Brent_Owens1 · February 9, 2007, 1:05am

yes you should include all the namespaces for every tag that uses one. So if you are using <ogc:bbox> you should include the ogc namespace.

cheers,

Brent Owens
(The Open Planning Project)

Sim Kim Sia wrote:

Hi Brent,

thanks for your reply.

I saw the following in a webpage http://docs.codehaus.org/display/GEOSDOC/1+How+a+GetFeature+Request+Works

filter=<ogc:Filter
xmlns:ogc="http://ogc.org" xmlns:gml="http://www.opengis.net/gml">
<ogc:BBOX>
<ogc:PropertyName>the_geom</ogc:PropertyName>
<gml:Box srsName="http://www.opengis.net/gml/srs/epsg.xml">
<gml:coordinates>-73.99312376470733,40.76203427979042 -73.9239210030026,40.80129519821393</gml:coordinates>
</gml:Box>
</ogc:BBOX>
</ogc:Filter>Granted that the example shown was using a wfs, instead of a wms.Should I include the Filter xmlns:ogc tags as well?Thank you.Regards
Kim Sia

Extension: 3160
----- Original Message ----- From: "Brent Owens" <brentowens@anonymised.com>
To: "Sim Kim Sia" <simksi@anonymised.com>
Cc: "Andrea Aime" <aaime@anonymised.com>; "geoserver users" <geoserver-users@lists.sourceforge.net>
Sent: Friday, February 09, 2007 12:13 AM
Subject: Re: [Geoserver-users] SQL select statements to generate query layers

Hi Kim,

For your <PropertyIsEqualTo> filter, you will need several <and> tags wrapped around it to capture both person, city, and address.

In javascript you can just create a string and interject variables into it like so:

var person = "Joe"
var address = "123 Fake St."
var city = "Victoria"

var filter = " <ogc:Filter>"+
           "<ogc:And>"+
           "<ogc:PropertyIsEqualTo>"+
                       "<ogc:PropertyName>Person</ogc:PropertyName>"+
                       "<ogc:Literal>"+person+"</ogc:Literal>"+
                   "</ogc:PropertyIsEqualTo>"+
           "</ogc:And>"+
           "<ogc:PropertyIsEqualTo>"+
                       "<ogc:PropertyName>Address</ogc:PropertyName>"+ <cid:part1.04050203.07070904@anonymised.com>
                       "<ogc:Literal>"+address+"</ogc:Literal>"+
                   "</ogc:PropertyIsEqualTo>"+
           "</ogc:And>"+
           "<ogc:PropertyIsEqualTo>"+
                       "<ogc:PropertyName>City</ogc:PropertyName>"+
                       "<ogc:Literal>"+city+"</ogc:Literal>"+
                   "</ogc:PropertyIsEqualTo>"+
           "</ogc:And>"+
       "</ogc:Filter>";

Then send that filter along with your request.

Hope that helps,

Brent Owens
(The Open Planning Project)

Andrea Aime wrote:

Sim Kim Sia ha scritto:

Hi Andrea,

thanks for your help.

From what I see,

the OGC filter requires me to specify a fixed value for the <literal> tag.

eg
     <PropertyIsEqualTo>

        <PropertyName>Person/Address/City</PropertyName>

        <Literal>Toronto</Literal>

     </PropertyIsEqualTo>

In the example above, I need to specify Toronto in advance.

Is there a way for OGC get a value that is determined by a javascript variable?

You can build the filter client side in javascript by string concatenation, so yes, you can do that. Unfortunately, I never coded in
javascript, so I can't provide you a sample. Keep the mails on the users mailing list, maybe someone else is able to help you.

Oh, btw, you can't use something like "Person/Address/City" in
Geoserver, since we don't support complex features (feature with nested
attributes). If you have a City attribute in the feature type you have to use <PropertyName>City</PropertyName>.

Cheers
Andrea

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

Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier.
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642

_______________________________________________
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

[This e-mail is confidential and may be priviledged. If you are not the
intended recipient, please kindly notify us immediately and delete the message
from your system; please do not copy or use it for any purpose, nor disclose
its contents to any other person. Thank you.]
---ST Electronics Group---