[Geoserver-users] Working with tables from two data sources

GeoServer GeoServer User
1

Hello

I have geoserver 2.4 with the spatial data in a postgresql+postgis database
and some additional alphanumerical data in a corporate oracle 11 database.

For some layers, I need to access data in the oracle database and join it
with the spatial data to do the right representation. I'll leave an example:

PostgreSQL -> table "places" (id, name, corp_code, the_geom)

Oracle11 -> table "CORP_PLACES" (code, status)

I have both databases set as datasources and both tables published. But for
the WMS service of "places" I need to style them more or less like this: if
status is 1, then the symbol must be red; if status is blue, then the symbol
must be green.

How can I combine the data from the two different data sources to publish it
together?

Thank you

2

On Thu, Feb 27, 2014 at 9:36 AM, Guillermo Heras <gheras@anonymised.com> wrote:

Hello

I have geoserver 2.4 with the spatial data in a postgresql+postgis database
and some additional alphanumerical data in a corporate oracle 11 database.

For some layers, I need to access data in the oracle database and join it
with the spatial data to do the right representation. I'll leave an
example:

PostgreSQL -> table "places" (id, name, corp_code, the_geom)

Oracle11 -> table "CORP_PLACES" (code, status)

I have both databases set as datasources and both tables published. But for
the WMS service of "places" I need to style them more or less like this: if
status is 1, then the symbol must be red; if status is blue, then the
symbol
must be green.

How can I combine the data from the two different data sources to publish
it
together?

GeoServer is now able to do joins from two different data source (yet, if
someone
wants to sponsor it, that would be great), but I believe you can create a
joining
view in Postgresql using the foreign data wrappers:
http://wiki.postgresql.org/wiki/Foreign_data_wrappers

Cheers
Andrea

--
== Our support, Your Success! Visit http://opensdi.geo-solutions.it 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

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

3

Andrea Aime <andrea.aime <at> geo-solutions.it> writes:

GeoServer is now able to do joins from two different data source (yet, if

someone

wants to sponsor it, that would be great), but I believe you can create a

joining

view in Postgresql using the foreign data wrappers:
http://wiki.postgresql.org/wiki/Foreign_data_wrappers

Thank you! I'll take a look. Yes, if I can connect to Oracle from Postgre
then there would be no problems.

4

I believe Andrea wanted to say “GeoServer is not able to…”

···

Regards,
Simone Giannecchini

Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Simone Giannecchini
@simogeo
Founder/Director

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

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

On Thu, Feb 27, 2014 at 9:50 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

Flow-based real-time traffic analytics software. Cisco certified tool.
Monitor traffic, SLAs, QoS, Medianet, WAAS etc. with NetFlow Analyzer
Customize your own dashboards, set traffic alerts and generate reports.
Network behavioral analysis & security monitoring. All-in-one tool.
http://pubads.g.doubleclick.net/gampad/clk?id=126839071&iu=/4140/ostg.clktrk

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

On Thu, Feb 27, 2014 at 9:36 AM, Guillermo Heras <gheras@anonymised.com96…> wrote:

Hello

I have geoserver 2.4 with the spatial data in a postgresql+postgis database
and some additional alphanumerical data in a corporate oracle 11 database.

For some layers, I need to access data in the oracle database and join it
with the spatial data to do the right representation. I’ll leave an example:

PostgreSQL → table “places” (id, name, corp_code, the_geom)

Oracle11 → table “CORP_PLACES” (code, status)

I have both databases set as datasources and both tables published. But for
the WMS service of “places” I need to style them more or less like this: if
status is 1, then the symbol must be red; if status is blue, then the symbol
must be green.

How can I combine the data from the two different data sources to publish it
together?

GeoServer is now able to do joins from two different data source (yet, if someone
wants to sponsor it, that would be great), but I believe you can create a joining
view in Postgresql using the foreign data wrappers:
http://wiki.postgresql.org/wiki/Foreign_data_wrappers

Cheers
Andrea

–

== Our support, Your Success! Visit http://opensdi.geo-solutions.it 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