2

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?

1 Answer 1

2

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);
5
  • 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. Mar 13, 2015 at 14:57
  • Sorry, but I didnt got your point (a). Could you please elaborate it more please? Mar 13, 2015 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, 2015 at 15:58
  • Actually I am confused as to what we have on production - PostGIS datatype or PostgreSQL datatype? Mar 13, 2015 at 16:05
  • it seems that it is postgres' Point type. postgis.net/docs/AddGeometryColumn.html
    – nickves
    Mar 13, 2015 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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