current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 2 down vote favorite

Recently, I developed a feature to save and fetch points(lat and lon) from DB. It is working fine on my local, but on production database it is giving error:

PG::UndefinedFunction: ERROR:  operator does not exist: point = unknown
LINE 1: ...s"."name" = '九龍灣' AND "region_points"."lonlat" = 'POINT(1...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT COUNT(*) FROM "region_points"  WHERE "region_points"."name" = '九龍灣' AND "region_points"."lonlat" = 'POINT(114.20910358428955 22.321574434895677)'


I digged into psql and found that there is difference in type for the columns.

On production:

production=> \d region_points
                                     Table "public.region_points"
   Column   |            Type             |                         Modifiers                          
------------+-----------------------------+------------------------------------------------------------
 id         | integer                     | not null default nextval('region_points_id_seq'::regclass)
 name       | character varying(255)      | 
 created_at | timestamp without time zone | 
 updated_at | timestamp without time zone | 
 lonlat     | point                       | 
Indexes:
    "region_points_pkey" PRIMARY KEY, btree (id)
    "index_region_points_on_lonlat" gist (lonlat)


Whereas, type of lonlat on my local machine is: geography(Point,4326)

development=# \d region_points;
                                     Table "public.region_points"
   Column   |            Type             |                         Modifiers                          
------------+-----------------------------+------------------------------------------------------------
 id         | integer                     | not null default nextval('region_points_id_seq'::regclass)
 name       | character varying(255)      | 
 created_at | timestamp without time zone | 
 updated_at | timestamp without time zone | 
 lonlat     | geography(Point,4326)       | 
Indexes:
    "region_points_pkey" PRIMARY KEY, btree (id)
    "index_region_points_on_lonlat" gist (lonlat)


My questions are:

  1. Is type wrong on my local or on production DB?
  2. How can I fix this?
  3. What is the difference between point and geography(Point,4326)?


I checked postgis_full_version on local and production DB's. Here are the outputs:

Production:

POSTGIS="2.1.4 r12966" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" LIBJSON="UNKNOWN" (core procs from "2.1.5 r13152" need upgrade) RASTER (raster procs from "2.1.5 r13152" need upgrade)


Development(local):

POSTGIS="2.1.5 r13152" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER

Is the error because of different versions?

share|improve this question
up vote 2 down vote

a) You are using the build-in postgresql's geometry data type and not Postgis Point data type. I would follow the production specifications. There are some VERY good reasons to switch the production db to use postgis datatypes but in production enviroments that is not always fearsable.

b) you can cast from point to geometry/geography and vice versa

select ST_geometryFromText('POINT(-118.4079 33.9434)')::point
select point(10.1,1)::geometry
select point(10.1,1)::geometry::geography

c) Point is geometry type while geography Points is geography

Point Geometry features represent a point in a nd space usually in 2D space (x,y). The units of x,y are governed from the srid. The distance between two points is calculated by the euclidian distance.

On the other hand geography Points are points that represent locations in a sphere and the distance between is calculated by great circles functions.

enter image description here

You can cast from geometry to geography if and only if the srid of the SRID of the geometry SRID is set to 4326 :

select ST_GeometryFromText('POINT(-118.4079 33.9434)', 4326)::geography

the opposite:

select ST_GeographyFromText('POINT(-118.4079 33.9434)')::geometry

note that an SRID if 4326 is implied

Update The Geometric operators between postgresql Point data types are a bit different. You can find a complete list here.

The "=" operator checks between areas. Since your geometries are points they dont have an area. Instead what you want "~="

so the query in your comment would be : 

SELECT COUNT(*) FROM "region_points"  WHERE "region_points"."name" = '九龍灣' AND "region_points"."lonlat" ~= POINT(114.20910358428955,22.321574434895677);
share|improve this answer
    
Thanks for detailed answer @nickves. I tried your suggestion of casting. SELECT COUNT(*) FROM "region_points" WHERE "region_points"."name" = '九龍灣' AND "region_points"."lonlat" = POINT(114.20910358428955,22.321574434895677); but it gives me error ERROR: operator does not exist: point = point. – varunvlalan Mar 13 '15 at 14:57
    
Sorry, but I didnt got your point (a). Could you please elaborate it more please? – varunvlalan Mar 13 '15 at 15:34
    
if you are asking about the pros/cons of using postgis datatypes over the native geometry datatypes that is another question that I cannot fully answer. The second part is pretty straight forward. If you change something in a production server you are going to break something somewhere else, and your service will be halted until you find it and fix it. Your boss wont like that at all – nickves Mar 13 '15 at 15:58
    
Actually I am confused as to what we have on production - PostGIS datatype or PostgreSQL datatype? – varunvlalan Mar 13 '15 at 16:05
    
it seems that it is postgres' Point type. postgis.net/docs/AddGeometryColumn.html – nickves Mar 13 '15 at 19:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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