3

i have a layer published on geoserver based on a connection to a postgres/postgis Table and i need to add another table existing on sql server to do a data jointure table to table in order to add external data to my layer..

is there any way to do it?

tnks in advance to your Help.

4
  • Did I understand this correcty? You want to join a table stored in MS SQL Server with table in PostgreSQL?
    – Shaunak
    Jun 10, 2013 at 13:36
  • yes in fact i would like to use informations stored in sql server for labeling my layer hows comming throught postgres so to do that i need to join two tables ..
    – Ben Ayoub
    Jun 10, 2013 at 13:42
  • 1
    What you are asking is not possible on database levels ofcourse and as far as I know GeoServer doesn't support this out of the box. That being said i can think of several ways to get data from different databases to show up on a single map. Could you please elaborate a little more on your front end? and how you are rendering this map? Are you using OpenLayers? What kind of layer is it that you want to label? a Point layer? I think this is more of a architecture problem than one that can be solved using something out of the box.
    – Shaunak
    Jun 10, 2013 at 13:49
  • i use openlayers to comsume WMS layers. this layers are published from postgres/postgis but there associated data are stored in sql server and there is common columns between both tables to relate them, so to enrich my map i would like to add labels to my layers coming throught data from sql server (using SLDs of course) tnks :)
    – Ben Ayoub
    Jun 10, 2013 at 14:07

2 Answers 2

1

Not a scalable solution. WMS is light weight, geojson at some point will become too heavy for web application (try making a geojson of say 100,000 point and load it on web using openlayer now do the same with WMS, you will see the difference).

Better solution is to foreign data wrappers postgres have extension that can add the tables directly from other databases. Then you can easily make view in postgres using foreign tables + native tables. this you don't even need to use 2 separate stores in geoserver.

So this is basically connect one database to another.

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

https://www.mssqltips.com/sqlservertip/3663/sql-server-and-postgresql-foreign-data-wrapper-configuration--part-3/

0

Based on your explanation , here is one way I can think of that should be simplest in your setup. You have Geoserver configured to publish your tables in postgres. Do the same with sqlserver tables. So now you have two separate layers published in Geoserver. Now because you havent mentioned what kind of geometry your data is, I will assume for example the data in postgres is Points. Now instead showing them on map using WMS, you can use the GeoJSON end point that geoserver offers to render these points. You can use the GeoJSON end point from your sqlserver layer too in JavaScript to use your foreign key and label the points in OpenLayers using it. Now things get a little complicated if your geometry is Polygons. but then too you can use same approach and use SLDs on client side to achive desired styling once you have made data association in JavaScript.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.