On 2012-08-06 16:56, Matthew Foster wrote:
On Sat, Aug 4, 2012 at 10:40 AM, Andrea Aime
<[email protected] <mailto:[email protected]>>
wrote:
On Fri, Aug 3, 2012 at 7:00 PM, Matthew Foster
<[email protected] <mailto:[email protected]>> wrote:
I have a PostGIS db data source on which I need to join in
some denormalized tables (i.e. it would have one-to-many
relationships). If I want to display this source via
Geoserver, am I going to have to use an Application Schema to
handle the denormalized tables?
Application schema is to be used if you need to come out with a
GML compatible complex schema in WFS,
otherwise you are probably better off using a SQL view, and paste
there the SQL query representing the
source of your data
I probably didn't explain my need adequately. I may have also used
the wrong term: "normalized" vs. "denormalized".
I have table 'A' that has weather stations like this...
Station ID the_geom
12345 POINT
54321 POINT
56789 POINT
Then we have table 'B' that has the weather equipment for each station
like this...
Station ID Equipment
12345 Thermometer
12345 Rain gauge
12345 Anemometer
54321 Rain gauge
56789 Thermometer
56789 Anemometer
So when I join table 'A' to table 'B', which I have done in a view on
the database itself, you get multiple rows for station '12345' and
'56789'. I am using WFS to query the station info on-click from a WMS
layer, and the query returns multiple feature IDs for the stations
that have more than one piece of equipment, as above.
What is the proper solution for this situation?
Like Andrea said, you could use a complex schema. When you use a SQL
view you can try a trick like this to avoid multiple rows:
http://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query
Another way is to avoid the join altogether and create a server-side
script to get the equipment for a Station ID from the database table and
call that using Ajax when your station is clicked. If your application
is already 'dynamic' this may not be that difficult to add, but the
specifics depend on your server technology.
Matthijs
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
