[Geoserver-users] CSV and XLS

Hello everyone,

Some time ago I was asked if it was possible to import CSV and/or XLS files into Geoserver. This because there was some 3rd party software that was able to just that, but requirements stated Geoserver has to be used for presentation of the data. I’m doing some research on this subject but was wondering what this community has to say on this subject. Would it be an absolute no-go or are there some smart plugins or ways to do this? Has anyone done it before? If so, how?

···


Best regards,
Martijn Coenen

I had a similar situation. I created a vrt file that pointed to my csv file, then ran a one line ogr script to convert the vrt into a shapefile, which GeoServer read fine. My vrt and ogr command line are as follows:

C:\data\dataout.csv wkbPoint

ogr2ogr -overwrite -f “ESRI_Shapefile” -a_srs EPSG:26957 C:\data\interp\airtemps.shp C:\data\dataout.vrt

  • John

John Callahan
Research Scientist
Delaware Geological Survey
University of Delaware
http://www.dgs.udel.edu

john.callahan@anonymised.com

On Thu, Apr 4, 2013 at 10:41 AM, Martijn Coenen - Covadis <Martijn@anonymised.com> wrote:

Hello everyone,

Some time ago I was asked if it was possible to import CSV and/or XLS files into Geoserver. This because there was some 3rd party software that was able to just that, but requirements stated Geoserver has to be used for presentation of the data. I’m doing some research on this subject but was wondering what this community has to say on this subject. Would it be an absolute no-go or are there some smart plugins or ways to do this? Has anyone done it before? If so, how?



Best regards,
Martijn Coenen


Minimize network downtime and maximize team effectiveness.
Reduce network management and security costs.Learn how to hire
the most talented Cisco Certified professionals. Visit the
Employer Resources Portal
http://www.cisco.com/web/learning/employer_resources/index.html


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

On Thu, Apr 4, 2013 at 5:17 PM, John Callahan <john.callahan@anonymised.com> wrote:

I had a similar situation. I created a vrt file that pointed to my csv file, then ran a one line ogr script to convert the vrt into a shapefile, which GeoServer read fine. My vrt and ogr command line are as follows:

<OGRVRTDataSource>
    <OGRVRTLayer name="dataout">
        <SrcDataSource>C:\data\dataout.csv</SrcDataSource>
<GeometryType>wkbPoint</GeometryType>
<GeometryField encoding="PointFromColumns" x="x" y="y" z="value"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

ogr2ogr -overwrite -f "ESRI_Shapefile" -a_srs EPSG:26957 C:\data\interp\airtemps.shp C:\data\dataout.vrt

I think the point would be to have vrt and other ogr formats
straightly available in GeoServer without converting data to
shapefiles or whatever.
Dear GeoServer developers, just for the sake of curiosity, is there
any plans to integrate OGR in the GDAL plugin at some point?
thanks
p

--
Paolo Corti
Geospatial software developer
web: http://www.paolocorti.net
twitter: @capooti
skype: capooti

On Thu, Apr 4, 2013 at 5:36 PM, Paolo Corti <pcorti@anonymised.com> wrote:

I think the point would be to have vrt and other ogr formats
straightly available in GeoServer without converting data to
shapefiles or whatever.
Dear GeoServer developers, just for the sake of curiosity, is there
any plans to integrate OGR in the GDAL plugin at some point?

A couple of years ago I wrote a working OGR data store in GeoTools:
https://github.com/geotools/geotools/tree/master/modules/unsupported/ogr

Unfortunately the module failed to gain any traction, there are a number of
difficulties:
* It's completely impossible to predict whether the module will compile and
pass unit
  tests against a random system, since we don't know what version of OGR is
around
  (too new? too old?), this prevented it so far from entering the official
build
* Since I wrote it, I had exactly zero use of it. As much as people rave
for OGR capabilities,
  large production sites don't use the formats that OGR supports, and stick
to spatial databases
  instead, meaning there is no commercial interest in it. In turns, this
means the module
  maintainership cannot be done during working hours.
* Native libraries in generally are not welcomed in Java production sites,
because a small
  glitch in them under sustained use that makes them segfault brings down
the entire
  java process. It does not help that heavy users of OGR are short lived
programs
  (cgi or fastcgi restarted every 1000 requests), desktop applications, and
in general,
  programs that have no multithreading (now GDAL has a setup that allows to
keep
  certain formats running in separate processes, which would address this,
I'd be happy
  to add support for it if there is anybody interested in sponsoring the
work).

That said, I guess I could make an extra effort to make the module
available for nightly
downloads in the community section:
http://gridlock.opengeo.org/geoserver/2.3.x/community-latest/

Is there anyone that would be interested in having it? No promises it will
be maintained,
no promises it works at all (haven't used it in 2 years).
But the source code is there, if there is someone that wants to move from
chit-chat
to action (as in, try to maintain it), I'm ready to help get them started.

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

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

Hi Andrea

On Sun, Apr 7, 2013 at 5:53 PM, Andrea Aime
<andrea.aime@anonymised.com> wrote:

A couple of years ago I wrote a working OGR data store in GeoTools:
https://github.com/geotools/geotools/tree/master/modules/unsupported/ogr

nice, didn't realize this

Unfortunately the module failed to gain any traction, there are a number of
difficulties:
* It's completely impossible to predict whether the module will compile and
pass unit
  tests against a random system, since we don't know what version of OGR is
around
  (too new? too old?), this prevented it so far from entering the official
build

Maybe it would be useful to stick to the latest GDAL stable release.
Or maybe, should it be possible that each user would compile it using
its GDAL version as it is done for other software using GDAL
(MapServer, QGIS etc)?
(not sure about Java downsides about this)

* Since I wrote it, I had exactly zero use of it. As much as people rave for
OGR capabilities,
  large production sites don't use the formats that OGR supports, and stick
to spatial databases
  instead, meaning there is no commercial interest in it. In turns, this
means the module
  maintainership cannot be done during working hours.

True, but this would open to GeoServer to a plethora of formats such
as csv, CouchDB, GFT, OSM and others, plus the ability to query RDBMS
not having direct geometric information through virtual formats.

* Native libraries in generally are not welcomed in Java production sites,
because a small
  glitch in them under sustained use that makes them segfault brings down
the entire
  java process. It does not help that heavy users of OGR are short lived
programs
  (cgi or fastcgi restarted every 1000 requests), desktop applications, and
in general,
  programs that have no multithreading (now GDAL has a setup that allows to
keep
  certain formats running in separate processes, which would address this,
I'd be happy
  to add support for it if there is anybody interested in sponsoring the
work).

I see the point, and that it is in fact what I thought it is the major
concern against using GDAL straight in Java.

That said, I guess I could make an extra effort to make the module available
for nightly
downloads in the community section:
http://gridlock.opengeo.org/geoserver/2.3.x/community-latest/

Is there anyone that would be interested in having it? No promises it will
be maintained,
no promises it works at all (haven't used it in 2 years).
But the source code is there, if there is someone that wants to move from
chit-chat
to action (as in, try to maintain it), I'm ready to help get them started.

I would be interested :slight_smile:

thanks a lot
p

--
Paolo Corti
Geospatial software developer
web: http://www.paolocorti.net
twitter: @capooti
skype: capooti

On Mon, Apr 8, 2013 at 10:06 AM, Paolo Corti <pcorti@anonymised.com> wrote:

> That said, I guess I could make an extra effort to make the module
available
> for nightly
> downloads in the community section:
> http://gridlock.opengeo.org/geoserver/2.3.x/community-latest/
>
> Is there anyone that would be interested in having it? No promises it
will
> be maintained,
> no promises it works at all (haven't used it in 2 years).
> But the source code is there, if there is someone that wants to move from
> chit-chat
> to action (as in, try to maintain it), I'm ready to help get them
started.
>

I would be interested :slight_smile:

Nice. I guess your first stop is to checkout the geotools sources from
trunk and try to
build them. In order to also include the ogr module in the build, you have
to add -Pogr
to the command line:

mvn clean install -Pogr

Once you have the jar for that module built (it will be in the module's
target directory), you can drop
it in a GeoServer master nightly build and if all is fine, it should just
work (otherwise... not, let me know)

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

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

On Mon, Apr 8, 2013 at 10:20 AM, Andrea Aime
<andrea.aime@anonymised.com> wrote:

Nice. I guess your first stop is to checkout the geotools sources from trunk
and try to
build them. In order to also include the ogr module in the build, you have
to add -Pogr
to the command line:

mvn clean install -Pogr

Once you have the jar for that module built (it will be in the module's
target directory), you can drop
it in a GeoServer master nightly build and if all is fine, it should just
work (otherwise... not, let me know)

Hi Andrea

I gave a try today, it looks no jar were built.
Looking at the build output, it seems there was an error:

[INFO] ------------------------------------------------------------------------
[INFO] Building OGR DataStore Module
[INFO] task-segment: [clean, install]
[INFO] ------------------------------------------------------------------------
[INFO] [clean:clean {execution: default-clean}]
[INFO] [git-commit-id:revision {execution: default}]
[INFO] [site:attach-descriptor {execution: default-attach-descriptor}]
[INFO] Preparing source:jar
[WARNING] Removing: jar from forked lifecycle, to prevent recursive invocation.
[INFO] [git-commit-id:revision {execution: default}]
[INFO] [source:jar {execution: attach-sources}]
[INFO] [install:install {execution: default-install}]
[INFO] Installing
/home/capooti/training/geoserver/geotools/geotools/modules/unsupported/ogr/pom.xml
to /home/capooti/.m2/repository/org/geotools/gt-ogr/10-SNAPSHOT/gt-ogr-10-SNAPSHOT.pom
[INFO] ------------------------------------------------------------------------
[INFO] Building Core OGR DataStore Module
[INFO] task-segment: [clean, install]
[INFO] ------------------------------------------------------------------------
[INFO] [clean:clean {execution: default-clean}]
[INFO] [git-commit-id:revision {execution: default}]
[INFO] [resources:resources {execution: default-resources}]
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory
/home/capooti/training/geoserver/geotools/geotools/modules/unsupported/ogr/ogr-core/src/main/resources
[INFO] [compiler:compile {execution: default-compile}]
[INFO] Compiling 12 source files to
/home/capooti/training/geoserver/geotools/geotools/modules/unsupported/ogr/ogr-core/target/classes
[INFO] [resources:testResources {execution: default-testResources}]
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 1 resource
[INFO] [compiler:testCompile {execution: default-testCompile}]
[INFO] Compiling 5 source files to
/home/capooti/training/geoserver/geotools/geotools/modules/unsupported/ogr/ogr-core/target/test-classes
[INFO] ------------------------------------------------------------------------
[ERROR] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Compilation failure
/home/capooti/training/geoserver/geotools/geotools/modules/unsupported/ogr/ogr-core/src/test/java/org/geotools/data/ogr/OGRPeformanceTest.java:[66,33]
cannot find symbol
symbol : constructor ShapefileDataStore(java.net.URL,boolean)
location: class org.geotools.data.shapefile.ShapefileDataStore

I am compiling vs GDAL 1.10. Any suggestion?

cheers
p

--
Paolo Corti
Geospatial software developer
web: http://www.paolocorti.net
twitter: @capooti
skype: capooti

On Mon, Apr 15, 2013 at 3:30 PM, Paolo Corti <pcorti@anonymised.com> wrote:

[INFO]
------------------------------------------------------------------------
[INFO] Building Core OGR DataStore Module
[INFO] task-segment: [clean, install]
[INFO]
------------------------------------------------------------------------
[INFO] [clean:clean {execution: default-clean}]
[INFO] [git-commit-id:revision {execution: default}]
[INFO] [resources:resources {execution: default-resources}]
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory

/home/capooti/training/geoserver/geotools/geotools/modules/unsupported/ogr/ogr-core/src/main/resources
[INFO] [compiler:compile {execution: default-compile}]
[INFO] Compiling 12 source files to

/home/capooti/training/geoserver/geotools/geotools/modules/unsupported/ogr/ogr-core/target/classes
[INFO] [resources:testResources {execution: default-testResources}]
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 1 resource
[INFO] [compiler:testCompile {execution: default-testCompile}]
[INFO] Compiling 5 source files to

/home/capooti/training/geoserver/geotools/geotools/modules/unsupported/ogr/ogr-core/target/test-classes
[INFO]
------------------------------------------------------------------------
[ERROR] BUILD FAILURE
[INFO]
------------------------------------------------------------------------
[INFO] Compilation failure

/home/capooti/training/geoserver/geotools/geotools/modules/unsupported/ogr/ogr-core/src/test/java/org/geotools/data/ogr/OGRPeformanceTest.java:[66,33]
cannot find symbol
symbol : constructor ShapefileDataStore(java.net.URL,boolean)
location: class org.geotools.data.shapefile.ShapefileDataStore

I am compiling vs GDAL 1.10. Any suggestion?

The shapefile datastore got recently swapped with a cleaned up version that
does not have as many
constructors as the old one.
The fix should be easy, just drop the second argument from the constructor
call, it's not necessary anyways

Cheers
Andrea

--

GeoServer training in Milan, 6th & 7th June 2013! Visit
http://geoserver.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

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