[Geoserver-users] Point Data in X,Y Fields in RDBMS

Hi

I have a SQL Server DB with point data stored in 2 Columns (X,Y)

Can these be mapped directly using GeoServer

Thanks,
Jerry

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Point-Data-in-X-Y-Fields-in-RDBMS-tp5082553.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

Hi Jerry,

In Oracle or PostGIS I would create a spatial view converting your textual values to a geometry field.

You should use a similar function in SQL Server and then publish the view on GeoServer.

Kind Regards,

Stefano

···

41.95581N 12.52854E

http://www.linkedin.com/in/stefanoiacovella

http://twitter.com/#!/Iacovellas

2013/10/9 osullivj <jeremiahosullivan@anonymised.com>

Hi

I have a SQL Server DB with point data stored in 2 Columns (X,Y)

Can these be mapped directly using GeoServer

Thanks,
Jerry


View this message in context: http://osgeo-org.1560.x6.nabble.com/Point-Data-in-X-Y-Fields-in-RDBMS-tp5082553.html
Sent from the GeoServer - User mailing list archive at Nabble.com.


October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk


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

Stefano

Thanks…the sql server db is 2005 so I don’t have geometry support available in the db

The only options I can see are

  1. Install postgres to pull the data from sql server every hour but th is seems a bit messy but might be the best option if I use geoserver
  2. investigate if there is an ogr data store for geoserver out there.
  3. Use Mapserver!

What do you think?

Thanks,
Jerry

···

41.95581N 12.52854E

http://www.linkedin.com/in/stefanoiacovella

http://twitter.com/#!/Iacovellas

2013/10/9 osullivj <jeremiahosullivan@anonymised.com>

Hi

I have a SQL Server DB with point data stored in 2 Columns (X,Y)

Can these be mapped directly using GeoServer

Thanks,
Jerry


View this message in context: http://osgeo-org.1560.x6.nabble.com/Point-Data-in-X-Y-Fields-in-RDBMS-tp5082553.html
Sent from the GeoServer - User mailing list archive at Nabble.com.


October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk


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

On Wed, Oct 9, 2013 at 1:19 PM, Jerry O'Sullivan <
jeremiahosullivan@anonymised.com> wrote:

Stefano

Thanks..the sql server db is 2005 so I don't have geometry support
available in the db

The only options I can see are
1) Install postgres to pull the data from sql server every hour but th is
seems a bit messy but might be the best option if I use geoserver
2) investigate if there is an ogr data store for geoserver out there.

An OGR data store is available, but you'll have to build it from sources:
https://github.com/geotools/geotools/tree/master/modules/unsupported/ogr

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

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

Andrea

Many thanks for the reply. It looks like we might be going to upgrade to SQL
Server in the near future so we can use that source directly.

However we need an interim solution to get the web mapping app running in
the short term (6 months)

What do you think of having a scheduled task to run an ogr2ogr command line
to pull out the data from the website into a shp file every x hours? I am
aware the .shp isn't recommended for production environments. As it is only
point data (@80,000 records) that needs to displayed and we will look at
caching the tiles for a few hours to improve performance would it be ok for
a few months?

Grateful for any advice you can give

Thanks,
Jerry

--
View this message in context: http://osgeo-org.1560.x6.nabble.com/Point-Data-in-X-Y-Fields-in-RDBMS-tp5082553p5082713.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

Jerry,

here are some instructions describing how a colleague used workarounds and CLR extensions to enable the delivery of spatial information with GeoServer and SQL Server 2005:
https://www.seegrid.csiro.au/wiki/Infosrvices/GeoserverMsSqlServer2005Connectivity

This worked well with WFS for simple features and app-schema complex features.

This approach has not been tested with WMS; I do not know if there are spatial indices, which are IIRC required by WMS.

Kind regards,
Ben.

On 09/10/13 19:19, Jerry O'Sullivan wrote:

Stefano

Thanks..the sql server db is 2005 so I don't have geometry support
available in the db

The only options I can see are
1) Install postgres to pull the data from sql server every hour but th
is seems a bit messy but might be the best option if I use geoserver
2) investigate if there is an ogr data store for geoserver out there.
3) Use Mapserver!

What do you think?

Thanks,
Jerry

On Oct 9, 2013 11:10 a.m., "Stefano Iacovella"
<stefano.iacovella@anonymised.com <mailto:stefano.iacovella@anonymised.com>> wrote:

    Hi Jerry,

    In Oracle or PostGIS I would create a spatial view converting your
    textual values to a geometry field.
    You should use a similar function in SQL Server and then publish the
    view on GeoServer.

    Kind Regards,

    Stefano

    ---------------------------------------------------
    41.95581N 12.52854E

    http://www.linkedin.com/in/stefanoiacovella

    http://twitter.com/#!/Iacovellas

    2013/10/9 osullivj <jeremiahosullivan@anonymised.com
    <mailto:jeremiahosullivan@anonymised.com>>

        Hi

        I have a SQL Server DB with point data stored in 2 Columns (X,Y)

        Can these be mapped directly using GeoServer

        Thanks,
        Jerry

        --
        View this message in context:
        http://osgeo-org.1560.x6.nabble.com/Point-Data-in-X-Y-Fields-in-RDBMS-tp5082553.html
        Sent from the GeoServer - User mailing list archive at Nabble.com.

        ------------------------------------------------------------------------------
        October Webinars: Code for Performance
        Free Intel webinars can help you accelerate application performance.
        Explore tips for MPI, OpenMP, advanced profiling, and more. Get
        the most from
        the latest Intel processors and coprocessors. See abstracts and
        register >
        http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk
        _______________________________________________
        Geoserver-users mailing list
        Geoserver-users@lists.sourceforge.net
        <mailto:Geoserver-users@lists.sourceforge.net>
        https://lists.sourceforge.net/lists/listinfo/geoserver-users

------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60134071&iu=/4140/ostg.clktrk

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

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineer
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

On Wed, Oct 9, 2013 at 10:39 PM, osullivj <jeremiahosullivan@anonymised.com>wrote:

Andrea

Many thanks for the reply. It looks like we might be going to upgrade to
SQL
Server in the near future so we can use that source directly.

However we need an interim solution to get the web mapping app running in
the short term (6 months)

What do you think of having a scheduled task to run an ogr2ogr command line
to pull out the data from the website into a shp file every x hours? I am
aware the .shp isn't recommended for production environments. As it is only
point data (@80,000 records) that needs to displayed and we will look at
caching the tiles for a few hours to improve performance would it be ok for
a few months?

A shapefile for 80000 points is going to be just fine

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

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