Create Buffer in meters with Spatialite query

[Hardik Pandya]

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 ?

[Alex Mandel]

On 03/04/2014 12:52 PM, Hardik Pandya wrote:
>
>
> 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 ?
>

Transform your data to a Projection that uses meters as the units. This
can be done on the fly by wrapping the geom column with the transform
and then wrapping that in the buffer command.

Is your data world wide? If not find a decent local projection in meters.

Thanks,
Alex

[Alex Mandel]

On 03/04/2014 01:24 PM, Hardik Pandya wrote:
> @Alex
>
> Thanks, do you have idea which SRID i have to use to convert this
> cordinates into meters ?
>
> Yes, My data is world wide. and because of that reason I can not pass the
> buffer value in degree.
>
> I have GPS data world wide, where they have not provided data into GIS form
> only latitude, longitude value there so using code I have converted data
> into GIS Geometry object form and insert into one table like
> LINESTRING(23.2161 69.95237, 23.21581 69.95279).
>
> Upto this working fine for Within, intersects kind function but not the
> "natural units" are in WGS 84 form so the buffer value taking as degree
> only.
>
> is SRID is change in different area on earth for WGS84 ? and to convert
> them into meters is there any formula ?
>

Please keep the discussion on list for the benefit of the whole community.

So the answer depends on what kind of error you're willing to introduce.
There are some world wide projections but they aren't great for distance
measurements, though at a 100m they might be fine.

In Postgis this is the kind of stuff they created a Geography type for
(data is all WGS 84, distances are all done with great circle in
meters), not sure what the spatialite equivalent is.

Another option is to bin the data by UTM zone and then apply the correct
utm zone transform to each subset within it.

Thanks,
Alex

[Hardik Pandya]

Sorry.

I joined Google groups before few hours only and not known how to reply so..

I am not using any GIS tool (PostGIS, QGIS).

I have just plain query which get me output and which output I am showing to users

[Alex Mandel]

Spatialite is a GIS. But what I'm suggesting is that Postgis can solve
your issue if you use it instead.

Here's an example of why using say Google/Spherical mercator wont
provide a suitable answer if you have any polar data.
http://googlemapsmania.blogspot.com/2013/04/mercator-projections-tissot-indicatrix.html

So unless you're going to slice your data into UTM zones for the
calculations you need a function that handles great circle distances as
part of the buffer. Postgis Geography data type does this.
http://postgis.net/docs/manual-2.1/using_postgis_dbmanagement.html#PostGIS_Geography

The Postgis Geometry is more like how Spatialite treats geometry where
you have to specify the projection for use specifically.

Note Spatilite does have Great Circle Length
http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.1.0.html
But that doesn't help you with the buffer operation. A new Great Circle
Buffer function or something similar would need to be created to handle
this use case.

Thanks,
Alex