Monday, October 14, 2013
SpatiaLite SQL statement to find points in a line buffer
A typical GIS spatial query is to find all the point features in a buffer around a linear feature. This can be easily achieved using SpatiaLite database also. The following illustrates the workflow using two SpatiaLite feature tables - nodes and segments.
Using any SpatiaLite interface (graphical or command line interface), simply type in the following SQL statement to create a 50 meter buffer around the line segment feature using the spatial operator ST_Buffer:
SELECT ST_Buffer(Geometry, 50) AS buffer
FROM segments
WHERE name='line1'
The screenshot below shows how the 50 meter buffer looks like on a map.
To identify all the point nodes within the buffer, we can wrap the buffer statement in a more complex SQL statement:
SELECT a.* FROM nodes AS a,
(
SELECT buffer FROM
(
SELECT ST_Buffer(Geometry, 50) AS buffer
FROM segments
WHERE name='line1'
) AS b
)
WHERE ST_Contains(buffer, a.Geometry)
The screenshot shows how the results are like on a map.
Subscribe to:
Post Comments (Atom)
6 comments:
I can't able to pass Buffer value in meters.
I am having WGS84 data and the passing value it's taking as degree only.
Can you please help me ?
http://stackoverflow.com/questions/21882853/gis-buffer-value-degree-to-meters-with-spatiallite
Nice Article !!!
I also having same requirement but
I can't able to create buffer with meter values.
Can you please help me ?
http://stackoverflow.com/questions/21882853/gis-buffer-value-degree-to-meters-with-spatiallite
Hi Hardikgis,
try this
select a.* from linka as a, (
select buffergeometry from (
select st_buffer(transgeometry, 500) as buffergeometry from
(
select st_transform(geometry,26910) as transgeometry from pointa
)
)
)
where st_contains(buffergeometry, a.geometry)
Just replace 26910 with your projected SRID, and 500 with your buffer distance in meters.
rg
Hello, Thanks for your reply.
Given query by you, not working as requirement.
I believe st_transform function not working with my spatialite because when I run below queries it's output is NULL
========
select st_transform(geometry,4326) as transgeometry from pointa
--------------------
select st_transform(geometry,26910) as transgeometry from pointa
==========
I have downloaded latest version of spatialite_gui which having Spatialite version 4.1.1
SQLite version 3.7.17
My Projected SRID is 4236 becuase I am having WGS84 data.
and I want to create 100 meter buffer.
=============
A.Geometry = LINESTRING(3.76379 50.80278, 3.76395 50.80292, 3.76409 50.80303, 3.76423 50.80314, 3.76439 50.80325, 3.76453 50.80333)
B.Geometry = LINESTRING(3.76468 50.80341, 3.76493 50.80352, 3.76512 50.80361)
currently distance between this two lines showing 0.005035 (aprrox you can say 560 meter)
=====================
So As Per my requirement(100 meter) it's not have to contains or intersects both geometry.
Your every sentence is appreciating. Thanks in advance
Hardikgis,
I am quite certain the sql syntax is correct, as I tested it on a real dataset. So, it must be either your data, or the software that is causing your erroneous result. Would you like to send a representative set of data to me by email or dropbox for me to look at?
Hello Dominoc,
Thankkkkkkkkkkkkkkkkk you very very very much !!!!!!!
You are Genius !!!
Post a Comment