CODEWITHSUNDEEP
sqlpostgresqlfunctiongeolocationpostgis
Julo0sS
Julo0sS

Reputation: 2102

Problems using functions on geography types with PostgreSQL - PostGIS (extension)

Here is the problem,

I recently installed postgresql with postGIS extension (with functions) I built a small database model with some geography data.

I'm actually trying to insert some geography data into it, and use functions on it... like points / polygons / ...

Problem is that when I try to use postGis functions like ST_Area, ST_Perimeter, ... PostgreSQL always returns error(s). Most of them are 42P01 meaning "unknown table" if I'm right. But tables exist...

Here is a screenshot of my actual test db :

http://s30.postimg.org/prnyyw7gh/image.png

You can see on the screenshot that the postGIS extension is active on my current model (the 1050 functions of this extension are available in this model)

I inserted data this way : 

INSERT INTO "Base".points (point,lat,lng) VALUES ("Base".ST_GeographyFromText('POINT(45.5555 32.2222)'),'45.5555','32.2222');
and
INSERT INTO "Base".polygons (polygon) VALUES ("Base".ST_GeographyFromText('POLYGON((x y,x1 y1,x2 y2,x y))'));

For table Points, I have a serial field (id), a geography field (point) and 2 text fields (lat, lng). For table Polygons, I have a serial field (id) and a geography field (polygon).

Here are the two queries I'm tryin to make : 

SELECT "Base".ST_Area(polygon) FROM "Base".polygons WHERE id=1
or
SELECT "Base".ST_Perimeter(polygon) FROM "Base".polygons WHERE id=1

These 2 tests do not work. They return error 42P01.

When tryin to test another function on my table "points", this also fails but returns a strange message. What I'm tryin is this : 

SELECT "Base".ST_Distance((SELECT point FROM "Base".points WHERE id=1),(SELECT point FROM "Base".points WHERE id=2))

This function exists, but returns error message SQL state: 42883 with message : 

ERROR : function _st_distance("Base".geography, "Base".geography, numeric, boolean) does not exist

I don't send any numeric or boolean... I can't explain where these errors are coming from...

I have to say that I'm new to postgresql... Problem may come from this...

Thanks for reading/Help

Upvotes: 0

Views: 2561

Answers (1)

mlinth
mlinth

Reputation: 3118

[Tip - post your create table statement, so we can see what the column types actually are].

I think your problems are because you are using geography data type rather than geometry; not all functions work (either at all or with the same arguments) for geography type. Here's an explanation of why - in short, according to the article.

If you do a lot of measurements and e.g. have to compare sizes of large polygons, it would make sense to use geography rather than geometry.

To find out what arguments are expected by which functions, check the postgis documentation. It will show you e.g.

float ST_Area(geometry g1);

float ST_Area(geography geog, boolean use_spheroid=true);

So you can see there are two versions of st_area. One accepts a geometry as an argument, the other accepts a geography, but you also have to add another argument.

If "polygon" is of type geography, you need

SELECT "Base".ST_Area(polygon, TRUE) FROM "Base".polygons WHERE id=1
-- true will measure around the geography spheroid and is more accurate
-- than false - read the documentation for what you need!

st_perimeter is similar.

Regarding your error on st_distance, did you see that underscore before "st_distance" in the error message? I suspect that you are running into problems because you have created the postgis extension in a schema. In PGAdmin, have a look for the function "ST_distance". You'd see that it calls in turn the function "_st_distance" - it can't find that because in your case the function is in a different schema. Try just doing this:

CREATE EXTENSION postgis

I think that will save you a world of pain.

Lastly, I think you have the lat and lon arguments in your st_geographyfromtext the wrong way round (but I may be wrong).

Upvotes: 3

Related Questions