Saving GeoJSON in Spatialite (Insert -- geosjon combo?)
Noli Sicad
Is there a way to save GeoJSON into Spatialite table?
Leaflet edits and saving it.
https://github.com/CloudMade/Leaflet/issues/333
Noli
Brad Hards
> Hi,
>
> Is there a way to save GeoJSON into Spatialite table?
http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html#p3misc
> Leaflet edits and saving it.
> https://github.com/CloudMade/Leaflet/issues/333
I don't know how Leaflet handles it, or if this meets your needs. If you need
to save more than just the geometry, I guess you'd need to handle this
yourself. I don't think there is a .loadgeojson type command.
Brad
Noli Sicad
>> Is there a way to save GeoJSON into Spatialite table?
> There is a conversion function GeomFromGeoJSON():
> http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html#p3misc
How to you use this one?
The problem is how do you interpret Geometry in OpenLayers or Leaftlet
to render in their canvases.
>> Leaflet edits and saving it.
>> https://github.com/CloudMade/Leaflet/issues/333
> I don't know how Leaflet handles it, or if this meets your needs. If you
> need
> to save more than just the geometry, I guess you'd need to handle this
> yourself. I don't think there is a .loadgeojson type command.
The edits can be converted to GeoJSON.
Anyway, I found this JSONSQL
http://trentrichardson.com/jsonsql/
If I am not mistaken, this will take JSON then convert it to SQL
statement, is this right?
Probably this can be modify to accept GeoJSON and Convert in SQL.
Noli
Noli Sicad
>> Leaflet edits and saving it.
>> https://github.com/CloudMade/Leaflet/issues/333
> I don't know how Leaflet handles it, or if this meets your needs. If you
> need
> to save more than just the geometry, I guess you'd need to handle this
> yourself. I don't think there is a .loadgeojson type command.
I am talking about the Spatialite library here, no access to
Spatialite Terminal (CLI) and using Javascript.
However, it is not probably bad to have this feature in Spatialite.
.loadgeojson
GeoJSON is "Linsanity" in Geo world this days.
Noli
Brad Hards
> Hi Brad,
Hi Noli,
> >> Is there a way to save GeoJSON into Spatialite table?
> >
> > There is a conversion function GeomFromGeoJSON():
> > http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html#p3misc
>
> How to you use this one?
The documentation shows that it takes one argument (the GeoJSON) and returns
the geometry:
GeomFromGeoJSON('{"type":"Point","coordinates":[-1,1,2.3]}')
or
GeomFromGeoJSON('{"type":"MultiPoint","crs":{"type":"name","properties":
{"name":"urn:ogc:def:crs:EPSG:3003"}},"bbox":[1,2,4,6],"coordinates":[[1,2,3],
[4,6,32]]}')
> The problem is how do you interpret Geometry in OpenLayers or Leaftlet
> to render in their canvases.
I'm not sure I understand the question. If you just need to pull stuff out of a
Geometry, we have several options, starting at ST_X / ST_Y / ST_Z.
> The edits can be converted to GeoJSON.
>
> Anyway, I found this JSONSQL
>
> http://trentrichardson.com/jsonsql/
>
> If I am not mistaken, this will take JSON then convert it to SQL
> statement, is this right?
I can't answer that, sorry.
BRad
Noli Sicad
>> How to you use this one?
> The documentation shows that it takes one argument (the GeoJSON) and returns
> the geometry:
> GeomFromGeoJSON('{"type":"Point","coordinates":[-1,1,2.3]}')
> or
>
> GeomFromGeoJSON('{"type":"MultiPoint","crs":{"type":"name","properties":
> {"name":"urn:ogc:def:crs:EPSG:3003"}},"bbox":[1,2,4,6],"coordinates":[[1,2,3],
> [4,6,32]]}')
>
>> The problem is how do you interpret Geometry in OpenLayers or Leaftlet
>> to render in their canvases.
> I'm not sure I understand the question. If you just need to pull stuff out
> of a
> Geometry, we have several options, starting at ST_X / ST_Y / ST_Z.
>
>> The edits can be converted to GeoJSON.
Just to clarify what I am trying to do about saving polygons, line,
etc from Openlayers and Leaflet. I have already accomplished rendering
spatialite geometry using WKT (or GeoJSON soon).
See this screenshots.
https://picasaweb.google.com/116847891529748214201/QGISOpenLayersAndSpatialiteTerminal#5657943833369879826
Now, the problem is how to save if I draw some polygons on the screen
and save it back to spatialite database. The purpose of the app is for
data collection in the field using mobile devices (iPad and Android
Tablets).
Noli
Brad Hards
> Now, the problem is how to save if I draw some polygons on the screen
> and save it back to spatialite database. The purpose of the app is for
> data collection in the field using mobile devices (iPad and Android
> Tablets).
(e.g. a data structure that represents the polygon for Carlton or a linestring
for Lygon Street :-)). Is that representation GeoJSON? or is it something
private?
You presumably got that out of spatialite with some kind of query on the
database and an output transformation (to turn the spatialite geometry into
something that you can render).
Basically, you're going to have to do the reverse transformation, then an
UPDATE (or perhaps INSERT, depending on your versioning approach).
Am I helping at all? If not, can you try to explain what you're already doing
in a bit more detail, and then what doesn't work?
Brad
Noli Sicad
> I assume that you have some intermediate representation in your application
> (e.g. a data structure that represents the polygon for Carlton or a
> linestring for Lygon Street :-)). Is that representation GeoJSON? or is it something
> private?
I was surprise you know Carlton and Lygon Street. I did not realise
that you are from Canberra.
I am in Melbourne as you seen in most of the screenshots in the picasa.
I got my shapefiles for Victoria in BRS and converted it into
spatialite. It is only by suburb, if Carlton is suburb, then I can
render it, using
Select asWKT name3 from Victoria where name3 = Carllon;
No, this is not private. Most of my data are viable in the net. OSM
has plenty of data if I want the street level polygon.
I have not done yet the AsGeoJSON query since Openlayers is bit fiddly, more
> You presumably got that out of spatialite with some kind of query on the
> database and an output transformation (to turn the spatialite geometry into
> something that you can render).
Yes, I just mentioned above, the asWKT query to get my polygon.
Openlayers has the example to render it in the Openlayers canvas.
Leaflet is better and easy to use that Openlayers.
> Basically, you're going to have to do the reverse transformation, then an
> UPDATE (or perhaps INSERT, depending on your versioning approach).
Yes, that it is going to do to create a mobile field collection device.
> Am I helping at all? If not, can you try to explain what you're already
> doing in a bit more detail, and then what doesn't work?
The solution how to save the drawing into SQLite/Spatialite.
Noli
Brad Hards
> > Basically, you're going to have to do the reverse transformation, then an
> > UPDATE (or perhaps INSERT, depending on your versioning approach).
>
> Yes, that it is going to do to create a mobile field collection device.
>
> > Am I helping at all? If not, can you try to explain what you're already
> > doing in a bit more detail, and then what doesn't work?
>
> The solution how to save the drawing into SQLite/Spatialite.
spatialite, you can extract it into GeoJSON or WKT (or EWKT, KML, GML,
whatever you like - Sandro's been very kind to us all :))
Leaflet or Openlayers can take some of those formats, and render them.
The question is about what the format is that you produce in your field
collection activity (point, polygon, linestring etc). Is it a sequence of
points in a different data structure? Or is it just more WKT or GeoJSON?
Brad
Noli Sicad
> I'm still not sure I understand the architecture. If you have the data in
> spatialite, you can extract it into GeoJSON or WKT (or EWKT, KML, GML,
> whatever you like - Sandro's been very kind to us all :))
>
> Leaflet or Openlayers can take some of those formats, and render them.
>
> The question is about what the format is that you produce in your field
> collection activity (point, polygon, linestring etc). Is it a sequence of
> points in a different data structure? Or is it just more WKT or GeoJSON?
In case of Openlayers that data format is quite mature (i.e. GeoJSON
or WKT (or EWKT, KML, GML).
http://openlayers.org/dev/examples/vector-formats.html
While in Leaflet is still evolving, nothing has done yet.
See this discussion here ( I am ns-1m in Github)
https://github.com/CloudMade/Leaflet/issues/333
What would be the best format for spatialite can handle inorder to
save these drawing in the spatialite database?
Noli
Brad Hards
question for Sandro.
Brad
Noli Sicad
I think I answered this question before.
I am getting old.
http://lists.osgeo.org/pipermail/openlayers-users//2011-August/021927.html
To save,
WKT:
INSERT INTO test_geom_Point
(id, name, date, measured_value, the_geom)
VALUES (NULL, 'first point', 1.23456,
GeomFromText('POINT(1.01 2.02)', 4326));
GeoJSON:
INSERT INTO test_geom_Point
(id, name, date, measured_value, the_geom)
VALUES (NULL, 'first point', 1.23456,
GeomFromGeoJSON('{"type":"Point","crs":{"type":"name",
"properties":{"name":"EPSG:4326"}},
"bbox":[-1.23455679,9.87654321,-1.23455679,9.87654321],
"coordinates":[-1.23455679,9.87654321]}'))
Noli
> --
> You received this message because you are subscribed to the Google Groups
> "SpatiaLite Users" group.
> To post to this group, send email to spatiali...@googlegroups.com.
> To unsubscribe from this group, send email to
> spatialite-use...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/spatialite-users?hl=en.
>
>
Brad Hards
> Brad,
>
> I think I answered this question before.
>
> I am getting old.
>
> http://lists.osgeo.org/pipermail/openlayers-users//2011-August/021927.html
advise on that interface.
Brad