[postgis-users] Need help on basic concepts, I just need really simple calculations

[Aaron Lewis]

Thanks Brent, I think I understand GIS a little bit now ...

Just two more questions,
1. When looking for points within a "circle", is this the recommended way?
    Which is,
    Storing data in `location geometry(point, 4326)` , convert it to
geography then do the calculation

2. I tried to create a GIST index and it's not working ... The
document says ST_DWithin uses index anyway.

gis=# create index user_loc_gist_idx on users using gist (location);
gis=# VACUUM ANALYSE;

gis=# explain SELECT * FROM users WHERE ST_DWithin (users.location,
st_setsrid(st_makepoint (45.3, 35.2), 4326), 100);



             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..1.80 rows=1 width=146)
   Filter: ((location &&
'0103000020E610000001000000050000009A99999999594BC033333333333350C09A99999999594BC06666666666E660409A999999992962406666666666E660409A9999999929624033333333333350C09A99999999594BC033333333333350C0'::geometry)
AND ('0101000020E61000006666666666A646409A99999999994140'::geometry &&
st_expand(location, 100::double precision)) AND _st_dwithin(location,
'0101000020E61000006666666666A646409A99999999994140'::geometry,
100::double precision))
(2 rows)







On Mon, Mar 23, 2015 at 4:14 AM, Brent Wood <pcreso at pcreso.com> wrote:
> Hi Aaron,
>
> Hopefully this (simplistic) description helps.
>
> Setting a SRID value for a feature tells Postgis what coordinate reference
> system (CRS) the coordinates are in. This includes the unit, which can be
> any linear unit of measurement, such as degrees (measured at the surface of
> the earth), meters, feet, etc. Obviously getting this right is important to
> calculate distances correctly.
>
> Different CRS's generally apply to different parts of the globe, and include
> the projection parameters. Note that every projection applies some
> distortion - trying to map (project) a part of a spherical surface to a flat
> plane has this problem. There are three main types of distortion - angular
> (changes shapes), area and distance. Normally to measure distance using a
> projected CRS, you'd use an equidistant projection (which minimises distance
> distortions) centered near the location you are trying to measure.
>
> An alternative approach is to measure it on a spheroid, in 3D space, instead
> of on a projected 2D space. This is basically what a Postgis geography
> allows you to do. But the coordinate units in a geography are degree
> coordinates, so you need to specify that your coordinates are unprojected
> lon/lat values when you use them in a geography. The SRID (Spatial Reference
> ID) for such a CRS is 4326.
>
> In your case, try:
> SELECT * FROM users
> WHERE ST_DWithin (users.location::geography, st_setsrid(st_makepoint (146.0,
> 138.19), 4326)::geography, 100);
>
> This assumes your location is a geometry with a SRID of 4326 (ie: the
> coordinate values are unprojected lon/lat degrees). It then converts this
> geometry to a geography datatype, which it tests against a point geometry in
> SRID 4326, which is also converted to a geography for the test, to see if it
> is within 100m. So this SQL tests geography against geography datatype.
>
> If your location feature is not SRID 4326, you'll need to reproject
> (transform) it to 4326 to for this to work:
>
> SELECT * FROM users
> WHERE ST_DWithin (ST_Transform(users.location,4326)::geography,
> st_setsrid(st_makepoint (146.0,
> 138.19), 4326)::geography, 100);
>
> (I haven't tested it, but I think this should work)
>
> Cheers
>
> Bent Wood
>
> ________________________________
> From: Aaron Lewis <the.warl0ck.1989 at gmail.com>
> To: postgis-users at lists.osgeo.org
> Sent: Monday, March 23, 2015 12:09 AM
> Subject: [postgis-users] Need help on basic concepts, I just need really
> simple calculations
>
> Hi,
>
> I've been searching online for days. Trying to understand why SRID is
> required. So I picked some random value.
>
> Now I'm need to retrieve POINTs within a circle range, e.g a circle at
> (146.0, 138.19) with radius of 100 meters:
>
> SELECT * FROM users
> WHERE ST_DWithin (users.location, st_setsrid(st_makepoint (146.0,
> 138.19), 2600), 100);
>
> It's very simple, but the result seems wrong. I have a record contains
> a POINT(55 43) that matches this query.
>
> Anyone know what's wrong with it?
>
> --
> Best Regards,
> Aaron Lewis - PGP: 0x13714D33 - http://pgp.mit.edu/
> Finger Print:  9F67 391B B770 8FF6 99DC  D92D 87F6 2602 1371 4D33
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>



-- 
Best Regards,
Aaron Lewis - PGP: 0x13714D33 - http://pgp.mit.edu/
Finger Print:   9F67 391B B770 8FF6 99DC  D92D 87F6 2602 1371 4D33