-1

I have two lon/lats that I've turned into geometry. I've tried having the column type being both text and geometry. I have a fence_geom and a user_geom. I am looking to test to see if the user is inside the fence_geom. I have tried using several different postGIS methods to check for this but I am only getting false. The location with the users is definitely within the geofence but I keep getting false even when the results should return true. For tests sake, every user has the same geometry which is the one within the geofence. I've tried switching the order of the parameters and the results are the same.

Table data types: TEXT(fence_geom, user_geom)

Value in fence_geom:

POLYGON((40.3329363811639 -111.742630004883,40.3324129517368 -111.64306640625,40.248611959397 -111.642379760742,40.3329363811639 -111.742630004883))

value in user_geom:

POINT(40.301282 -111.710769)

Versions: PostgreSQL: 9.66; PostGIS: 2.5

EDIT: From my readings I've come to the conclusion that I need an SRID. How would I add an SRID for a call like this?

 SELECT ST_Covers(fences.fence_geom, users.user_geom)
 FROM fences, users

This function returns an empty result.

  • You don't really need an SRID, they just have to have the same SRID. For example this works: SELECT st_covers(ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))'), ST_GeomFromText('POLYGON((2 2, 2 3, 3 3, 3 2, 2 2))')); Can you show us some geometries as text that should cover eachother? Use ST_AsText for this. You can set an srid with st_setsrid and transform it with st_transform. postgis.net/docs/ST_SetSRID.html postgis.net/docs/ST_Transform.html – Lennert De Feyter Dec 7 '17 at 3:06
  • It would help if you provided how you've turned the coordinates into geometry. Providing the exact version of PostgreSQL and PostGIS in use probably wouldn't help, except when it would. Please Edit the question. – Vince Dec 7 '17 at 3:09
  • @LennertDeFeyter Thanks! I'm so new to gis. Should it work the same way with the second parameter being a point? I have the type as text and I've manually turned all the coords into geom already so would I still need the GeomFromText? – Gaelic Laic Dec 7 '17 at 3:09
  • @Vince I did it with just a sql query on postico. So the data type is text and the text is the geometry of the coords – Gaelic Laic Dec 7 '17 at 3:10
  • Is the geometry type a text field? In that case, that won't work. You need a specific geometry column. – Lennert De Feyter Dec 7 '17 at 3:12
2

From you comments, I am assuming you are not creating a correct geometry table correctly.

I created the below example which creates 2 tables, inserts example data and works perfectly.

-- Table: public.parcels

DROP TABLE public.user;

CREATE TABLE public.user
(
  id serial,
  geom geometry(Geometry)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX user_geom_1508307179527
  ON public.user
  USING gist
  (geom);

DROP TABLE public.fences;
Create TABLE public.fences
(
  id serial,
  geom geometry(Geometry)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX fences_geom_1508307179527
  ON public.fences
  USING gist
  (geom);

insert into public.user(geom) values (ST_GeomFromText('POINT( 2 2)'));
insert into public.fences(geom) values (ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))'));

select a.id, b.id from public.fences a,  public.user  b where  st_covers(a.geom, b.geom);

I would suggest following a few tutorials, such as: http://www.bostongis.com/?content_name=postgis_tut01 http://postgis.net/workshops/postgis-intro/introduction.html

EDIT:

Also your example (even with flipped coordinates) don't overlap

enter image description here

EDIT 2 Example fiddle of what you are looking for: https://jsfiddle.net/qf4z28tt/1/

  • Could you explain a couple of these areas? I'm not following With(OIDS=false). The Index creation. As well as the insert into values? – Gaelic Laic Dec 7 '17 at 3:38
  • How were you able to test this? I would love to have a visualization! – Gaelic Laic Dec 7 '17 at 3:43
  • 1
    I visualised it with QGIS using the QuickWKT plugin. the OID is an internal id for postgres, not really needed: stackoverflow.com/questions/5625585/… Spatial indexing makes spatial queries go faster, just like any other indexing. revenant.ca/www/postgis/workshop/indexing.html And the insert into the table. A geometry is not stored as text, but in binary. So I use a function ST_GeomFromText to convert text into a geometry. PostGIS is not basic, you might want to start with toying around with a tool such as QGIS first. – Lennert De Feyter Dec 7 '17 at 3:44
  • I installed QGis. Do I use PostGis along with QGis? Also, so in your opinion, what would be the least complex way to create a method that takes a users constantly updated geolocation and testing it against several pre-set geoFences. I am trying to return whether a user is currently inside my set coordinates. – Gaelic Laic Dec 7 '17 at 4:04
  • Sorry for the silly question, but user of what? A website, the database, a custom application? Also, the scale is also important, how many fences are there? Postgis is a full on database, if you only have a couple of thousand fences, there is no need to use postgis. – Lennert De Feyter Dec 7 '17 at 4:08

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.