Re: [sqlite] Using SQLite for GIS Application

[stormtrooper]

This is just a simple spatial intersection. Each line will intersect many
contour lines producing points, you can calculate the average elevation
points per line, calc min, max elev. Or save the line ID and elevation
points in another table to query later. Ask the Spatialite User Group. 

http://groups.google.com/group/spatialite-users



Itzchak Raiskin wrote:
> 
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line
> and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this
> will
> be very time consuming as I have hundreds of lines with hundreds of
> points.
> Any suggestions?
> 
> Thanks, Itzik
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Using-SQLite-for-GIS-Application-tp25100408p25710287.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Jean-Christophe Deschamps]

I want to use SQLite in a GIS application where I create a database
>containing terrain data (coordinates, height).
>I would like to query this database with start and end points of a 
>line and
>get a vector with all heights point along this line.
>I can, of course create a query for each point along the line, but 
>this will
>be very time consuming as I have hundreds of lines with hundreds of 
>points.
>Any suggestions?

Google for "Voronoï diagrams" and "Delaunay triangularization". These 
are the very basic tools for modelling terrain at small scales (with 
small details). From then, if you need profile approximation, then you 
can use one of the many versions of "splines" or "parametric curves" to 
fit the data.

You're certainly better off using a proven dedicated extension for 
doing so.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Kit]

2009/8/23 P Kishor :
> If a line is expressed by (y = ax + c), you need to find all x,y that
> will satisfy that equation. So,
>
> SELECT x, y, z
> FROM terrain
> WHERE a*x - y + c = 0

General equation of line is a*x + b*y + c = 0. If you delete parameter
"b", you will have a problem with some pair of points - e.g.
[0,0],[0,1].

Better way to save unit squares to file is a simple 2D matrix. No
database. Fast and easy for samples with fixed steps of coordinates.
You can load entire matrix into memory.
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[P Kishor]

On Sun, Aug 23, 2009 at 12:08 AM, Itzchak
Raiskin wrote:
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?

or, you can build on the work already done by USGS for you (they have
worldwide data as well).

http://gisdata.usgs.gov/xmlwebservices2/elevation_service.asmx


>
> Thanks, Itzik
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[P Kishor]

On Sun, Aug 23, 2009 at 11:34 AM, P Kishor wrote:
> On Sun, Aug 23, 2009 at 11:11 AM, Simon
> Slavin wrote:
>>
>> On 23 Aug 2009, at 5:00pm, P Kishor wrote:
>>
>>> WHERE a*x - y + c = 0
>>
>> Here's the problem.  This works only when the equation is exact.
>
> Indeed. We already laid out those presumptions. One, your height
> coverage has to be continuous as you can get via an image. Two, you
> can substitute any equation for your line, however, any complex line
> can be broken up into segments of simple, straight lines. All GIS
> software do just that. Once you can solve the problem for a simple
> segment, you can repeat the solution for every segment in the line.
>
> Postgis/Spatialite by themselves won't solve the problem, but they may
> have (don't know about Spatialite, but Postgis has a boatload of
> geographic functions created by the developers) ready made functions
> that can help build the solution.
>
> A really nice application would allow the user to click, click, click
> a line (of many segments) on a representation of a terrain, go
> retrieve the height values, and construct an image of the elevation
> profile.
>

Kinda like http://veloroutes.org/bikemaps/


>
>> Which
>> under normal circumstances means that all the numbers fit neatly with some
>> imaginary integer formula.  This isn't how real life works, especially when
>> you've correctly noted that your measured heights weren't measured at
>> exactly regular intervals.
>>
>> You're going to have to do some proper programming.  For example, how to
>> interpolate the height at an arbitrary position when you have a collection
>> of heights measured nearby.  There's no easy solution and you certainly
>> can't do the majority of the work inside a SQL query.
>>
>> Simon.
>>
>
>
>
> --
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[P Kishor]

On Sun, Aug 23, 2009 at 11:11 AM, Simon
Slavin wrote:
>
> On 23 Aug 2009, at 5:00pm, P Kishor wrote:
>
>> WHERE a*x - y + c = 0
>
> Here's the problem.  This works only when the equation is exact.

Indeed. We already laid out those presumptions. One, your height
coverage has to be continuous as you can get via an image. Two, you
can substitute any equation for your line, however, any complex line
can be broken up into segments of simple, straight lines. All GIS
software do just that. Once you can solve the problem for a simple
segment, you can repeat the solution for every segment in the line.

Postgis/Spatialite by themselves won't solve the problem, but they may
have (don't know about Spatialite, but Postgis has a boatload of
geographic functions created by the developers) ready made functions
that can help build the solution.

A really nice application would allow the user to click, click, click
a line (of many segments) on a representation of a terrain, go
retrieve the height values, and construct an image of the elevation
profile.


> Which
> under normal circumstances means that all the numbers fit neatly with some
> imaginary integer formula.  This isn't how real life works, especially when
> you've correctly noted that your measured heights weren't measured at
> exactly regular intervals.
>
> You're going to have to do some proper programming.  For example, how to
> interpolate the height at an arbitrary position when you have a collection
> of heights measured nearby.  There's no easy solution and you certainly
> can't do the majority of the work inside a SQL query.
>
> Simon.
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Stephen Woodbridge]

Itzchak Raiskin wrote:
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?

You might want to look at SpatiaLite which is a GIS addon to SQLite and 
has most of the capabilities of PostGIS.

http://www.gaia-gis.it/spatialite/

-Steve
  http://imaptools.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Simon Slavin]

On 23 Aug 2009, at 5:00pm, P Kishor wrote:

> WHERE a*x - y + c = 0

Here's the problem.  This works only when the equation is exact.   
Which under normal circumstances means that all the numbers fit neatly  
with some imaginary integer formula.  This isn't how real life works,  
especially when you've correctly noted that your measured heights  
weren't measured at exactly regular intervals.

You're going to have to do some proper programming.  For example, how  
to interpolate the height at an arbitrary position when you have a  
collection of heights measured nearby.  There's no easy solution and  
you certainly can't do the majority of the work inside a SQL query.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[P Kishor]

On Sun, Aug 23, 2009 at 12:08 AM, Itzchak
Raiskin wrote:
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?
>


The problem to solve is that you have a discontinuous surface
(discrete points) with height values. You want to do a line-on-poly
overlay and find the surface values that intersect with the line. Of
course, the line may or may not intersect with any of your stored
points, so you have to do some interpolation.

If you can "fill" all the coords on a surface, typically done by an
image where every pixel holds the height value, you can find all the
points that coincide with a line.

If a line is expressed by (y = ax + c), you need to find all x,y that
will satisfy that equation. So,

SELECT x, y, z
FROM terrain
WHERE a*x - y + c = 0

But you can make the search more efficient by only searching within
the bounding box of the line, so, assuming xmin, ymin, xmax, ymax are
the end points of the line

SELECT x, y, z
FROM terrain
WHERE a*x - y + c = 0
  AND x BETWEEN xmin AND xmax
  AND y BETWEEN ymin AND ymax


In other words, Kit gave you the answer already. I hope my explanation
helped flesh it out more. You can use your own line equation in the
SELECT statement above.


-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Derrell Lipman]

On Sun, Aug 23, 2009 at 01:08, Itzchak Raiskin wrote:

> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this
> will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?
>

The "rtree" extension may be of use to you.
http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README
More information is available here: http://en.wikipedia.org/wiki/R-tree

Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Rich Shepard]

On Sun, 23 Aug 2009, Itzchak Raiskin wrote:

> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height). I would like to query this
> database with start and end points of a line and get a vector with all
> heights point along this line. I can, of course create a query for each
> point along the line, but this will be very time consuming as I have
> hundreds of lines with hundreds of points. Any suggestions?

Itzik,

   Other responders have suggested how to query for specific lines, but that
will not fulfill the requirements of a GIS. You could create a
computer-aided drafting (CAD) application this way, but you need to
incorporate coodinate geometry if you want a spatial analytical tool. You
need to accommodate a single edge that defines adjacent polygons and store
the spatial relationship. SQLite is not the best tool for spatial
applications.

   I suggest that you look at PostGIS, OpenGIS, GRASS, and other
well-developed applications. Yes, they use PostgreSQL for the data storage
and can store geometric objects as data entities.

   You are, of course, welcome to reinvent the wheel, but I suggest that you
learn something about spatial databases and coordinate geometry if you want
an application that actually works.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[John Machin]

On 23/08/2009 3:08 PM, Itzchak Raiskin wrote:
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?

Specify with some precision what tables of data you expect to have:

create table terrain_data (id, x, y, height) -- ??
create table line (?)

How is the terrain data expressed? I.e. are "cordinates" (lon, lat) or 
something else? Height above what in what units?

What is a "line"? 2D or 3D?

Note you say you have hundreds of lines but don't say how they are 
related to the terrain data ...

Explain "get a vector with all heights point along this line" in 
mathematical terms

Explain "for each point along the line"

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite for GIS Application

[Kit]

2009/8/23 Itzchak Raiskin :
> Hi
> I want to use SQLite in a GIS application where I create a database
> containing terrain data (coordinates, height).
> I would like to query this database with start and end points of a line and
> get a vector with all heights point along this line.
> I can, of course create a query for each point along the line, but this will
> be very time consuming as I have hundreds of lines with hundreds of points.
> Any suggestions?
> Thanks, Itzik

SELECT x,y,height FROM terrain WHERE round(a*x+b*y+c)=0
AND x BETWEEN xmin AND xmax
AND y BETWEEN ymin AND ymax;
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Using SQLite for GIS Application

[Itzchak Raiskin]

Hi
I want to use SQLite in a GIS application where I create a database
containing terrain data (coordinates, height).
I would like to query this database with start and end points of a line and
get a vector with all heights point along this line.
I can, of course create a query for each point along the line, but this will
be very time consuming as I have hundreds of lines with hundreds of points.
Any suggestions?

Thanks, Itzik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users