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.

6 comments:

Unknown said...

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

Unknown said...

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

dominoc925 said...

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

Unknown said...

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

dominoc925 said...

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?

Unknown said...

Hello Dominoc,

Thankkkkkkkkkkkkkkkkk you very very very much !!!!!!!

You are Genius !!!