2

I am new to Spatialite. I have following query:

select A.* from linka as A, pointa as B where Contains(Buffer(B.Geometry, 100), A.Geometry)

Here,

A.Geometry = LINESTRING(23.2161 69.95237, 23.21581 69.95279) B.Geometry = POINT(23.21817 69.95177)

I want to create 100 meters buffer and get to know which are the link's are contained by point geometry.

I am having coordinates of WGS 84 projection system from navigation data.

The passing 100 over here, it takes as degree and creating buffer like that, but I actually want to specify the buffer distance as meters. (I don't want to pass the distance in degree value because on each earth pole degree's buffer cover not same range area. This is the big problem of geodecy)

Is there any way to satisfy my requirement in Spatialite ?

  • Welcome to gis.SE. Are all of your features in the same general area? Can you show the bounding box for A.Geometry and B.Geometry? Something like SELECT ST_MinX(linka.Geometry), ST_MinY(linka.Geometry), ST_MaxX(linka.Geometry), ST_MaxY(linka.Geometry) or SELECT Extent(pointa.Geometry) should work. – BradHards Mar 14 '14 at 5:25
3

I think you will have to transform your table geometry to a projected coordinates system before doing your buffer for instance Sĥerical Mercator (EPSG:3857)

Note that Spherical Mercator projection wont be precise, so I suggest using a local projection.

see transform function here

ST_Transform( A.geometry , 3857 ) 

ST_Transform( B.geometry , 3857 )

you can do it in one query like this (not tried):

select A.* from linka as A, pointa as B where Contains(Buffer(ST_Transform( B.geometry , 3857 ), 100), ST_Transform( A.geometry , 3857 ))
  • Hello, Thanks for your reply. Given query by you, working as per requirement or not I am not sure because I believe st_transform function not working with my spatialite because when I run below querie it's output is NULL ======== **select st_transform(geometry,4326) from linka ** I have downloaded latest version of spatialite_gui which having Spatialite version 4.1.1 SQLite version 3.7.17 - can you able to help me with this ? – Hardik Mar 3 '14 at 14:58
  • ST_TRANSFORM() function works for me with spatialite 4.1.1 and spatialite gui 1.7.1 – Below the Radar Mar 3 '14 at 17:15
  • Note that the unit of EPSG:3857 (Mercator) is meters only in equator. In other latitudes it is bigger in real life, and different for latitude and longitude. If you want to use this (terrible) trick, then use some local area projection, which does not disturb so much. Proper solution would be to use spherical buffer calculation, which calculates directly from lat/long (4326), but unfortunately many GIS solutions do not include such function. – JaakL Jul 19 '16 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.