1

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)

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

I can able to find the inserted '100' is actually degree value and it's giving me output which are coming in this range.

I can put the degree value also in my query but the transformation from degree to meters/kilometers is not same all around the world. I gone through many sites and able to know 1 degree = 110 KM approx. but from GIS expert and some reference sites also get to know at each pole on earth it's different.

For instance, the difference at Alta/Norway between metrical x and y for planar approximation is 34 km in x direction equal 111 km in y direction. The buffer looks similar to this while using geographic coordinates:
http://extremelysatisfactorytotalitarianism.com/blog/wp-content/uploads/2010/08/tissot_indicatrix_equirectangular_proj.png

I build software which convert geographical data to geometrical (X, Y -coordinate format) data and make transformation where Spatiallite can understand.

I also trying to read regarding SRID things but not able to understand how to insert it into my query.

Paul Lammertsma
  • 37,593
  • 16
  • 136
  • 187
Hardik
  • 259
  • 1
  • 2
  • 17

2 Answers2

0

temporary transform your geometry to a metric projection (eg UTM) if i assume your current projection is WGS84 try the following statment

 transform (buffer (transform (B.geometry, #projection), #dist), 4326))

-in #projection: your new projection, eg: 32631 for WGS 84 / UTM zone 31N (choose the projection that fits your Zone)

-in #dist: distance in meters

(4326 for WGS84)

Chakib SAFAR
  • 66
  • 7
  • Hello Chakib,yes the current projection is WGS84. I have checked but looks like tranform function not working with my GUI although I am having latest one. select A.* from linka as A, pointa as B where Contains(Transform(Buffer(Transform(B.Geometry, 32631), 100), 4326), A.Geometry) Also, after this also as I am passing 100 in dist does it will going to take as meters ? – Hardik Feb 24 '14 at 09:40
  • I also could not able to understand that while we are passing this projection number '32631', '4326'. how they are working and how can we trust that it's transforming right ? do I need any extra table in my database ? – Hardik Feb 24 '14 at 09:43
  • I have data like below :: **select AsText(Geometry) from pointa** :: >> LINESTRING(3.76401 50.80294) ... NOW I want to create 100 meter buffers of this and want to know how many geometry intersecting or contains by it's buffer – Hardik Feb 24 '14 at 09:49
  • yes the result will be in meters, but you must choose the correct transformation to get correct results, I gave the projection (srid) 32631 as an example (java) to have the right srid I use this little algorithm: int zone =(int)(Math.floor((point.longitude+180)/6))+1; int srid=0; if(point.latitude>0) srid=32600+zone;else srid=23700+zone; to understand the UTM projection: http://fr.wikipedia.org/wiki/Transverse_Universelle_de_Mercator – Chakib SAFAR Feb 24 '14 at 22:01
  • Hi Chakib, yes the above formula can get me srid=32631 ... my point.longitude = 50.80294 .. and after calculation srid=32631 only. This point belongs from belgium and I have cross check on other sites where they mention srid=32631 only. I also used distance query on [link](http://stackoverflow.com/questions/12204834/get-distance-in-meters-instead-of-degrees-in-spatialite?rq=1) but not get any result... In addition, still not sure the same method can give output all around earth. – Hardik Feb 25 '14 at 09:35
-1

If You are using SQL server 2008 or later, You should be able to use spatial types

  1. lets assume linka contains geography column, and its name is geo, and it contains Points
  2. dont forget to create spatial index !

  3. try this

    DECLARE @buffer geography = geography::Point( 1.234, 5.678, 4326 ); DECLARE @distance float = 100.0;

    SELECT * from linka WHERE linka.geo.STDistance(@buffer) < @distance

GregS
  • 19
  • 1
  • OK, I see, spatialite is SQL Lite spatial something ;) but, there should be something similar – GregS Feb 19 '14 at 15:04
  • Greg, yes spatialite is not similar to SQL server, it's lacking with many functionality. My query also working fine just problem is, the passing distance it's taking into degree. – Hardik Feb 20 '14 at 08:57