Обсуждение: Postgres native geometry types

Поиск
Список
Период
Сортировка

Postgres native geometry types

От
Kasia Tuszynska
Дата:

Hi Everybody,

 

I am doing some testing on the postgres native geometry types, namely: point, line, lseg, box, path (closed), path [open], polygon and circle.

 

Does anyone know what are they being used for? PostGIS is usually designated as the gis “module” to postgres and there are other spatial types that work on postgres.

So, what are the native geometry types on postgres used for? http://www.postgresql.org/docs/9.0/static/datatype-geometric.html#AEN6332

 

I got the point, line, lseg, box and circle to wok but I am having some problems creating data with the path and polygon types. I am guessing that the postgres doc has not been updated lately. It says that the line function has not been implemented yet, but it works,  it also omits a crucial part in the usage.

It shows what I would interpret this usage: INSERT INTO gis.test_line (k_id, pg_line) VALUES (1, line((10,10),(20,20)));

 

Where in fact, in the types that I could get it to work I needed the following: INSERT INTO gis.test_line (k_id, pg_line) VALUES (1, line(point(10,10),point(20,20)));

 

Some variations I tried for path and polygon:

 

create table gis.test_path (k_id integer, pg_path path);

INSERT INTO gis.test_path (k_id, pg_path) VALUES (1, path((10,10),(10,20),(20,20), (20,10)));

 

create table gis.test_path2 (k_id integer, pg_path path);

INSERT INTO gis.test_path2 (k_id, pg_path) VALUES (1, path(point(10,10),point(10,20)));

 

create table gis.test_polygon (k_id integer, pg_poly polygon);

INSERT INTO gis.test_polygon (k_id, pg_poly) VALUES (1, polygon((10,10),(10,20),(20,20),(20,10)));

 

create table gis.test_polygon2 (k_id integer, pg_poly polygon);

INSERT INTO gis.test_polygon2 (k_id, pg_poly) VALUES (1, polygon(point(10,10),point(10,20),point(20,20),point(20,10)));

 

Anyone have any suggestions on these two types?

 

Thank you,

Sincerely,

Kasia

Re: Postgres native geometry types

От
Szymon Guz
Дата:
On 4 March 2011 22:58, Kasia Tuszynska <ktuszynska@esri.com> wrote:

Hi Everybody,

 

I am doing some testing on the postgres native geometry types, namely: point, line, lseg, box, path (closed), path [open], polygon and circle.

 

Does anyone know what are they being used for? PostGIS is usually designated as the gis “module” to postgres and there are other spatial types that work on postgres.

So, what are the native geometry types on postgres used for? http://www.postgresql.org/docs/9.0/static/datatype-geometric.html#AEN6332

 


Hi,
I think this part of PostGIS documentation describes the difference well http://postgis.refractions.net/documentation/manual-1.5/ch04.html#PostGIS_GeographyVSGeometry.

The main idea is that normal geometry types are using standard flat surface and normal (Euclidean) 3d space. 
The postgis types are using some spheroid defined in an appripriate WGS, this spheroid is similar to Earth shape and has its size in meters, not just virtual mathematical coordinates.

If you use the normal geometry types, you do it like you'd perform calculations in math, using some virtual points and distance calculated using the Pythagorean theorem.
Using postgis, you use points that are defined on some spheroid (depending on the WGS) and you provide longitude and latitude. The distance between two geographical points can be calculated in meters and is defined on surface of the spheroid, not in straight line like in math.

I hope that helps a little.

regards
Szymon

Re: Postgres native geometry types

От
Szymon Guz
Дата:


On 4 March 2011 22:58, Kasia Tuszynska <ktuszynska@esri.com> wrote:

Hi Everybody,

 

I am doing some testing on the postgres native geometry types, namely: point, line, lseg, box, path (closed), path [open], polygon and circle.

 

Does anyone know what are they being used for? PostGIS is usually designated as the gis “module” to postgres and there are other spatial types that work on postgres.

So, what are the native geometry types on postgres used for? http://www.postgresql.org/docs/9.0/static/datatype-geometric.html#AEN6332

 

I got the point, line, lseg, box and circle to wok but I am having some problems creating data with the path and polygon types. I am guessing that the postgres doc has not been updated lately. It says that the line function has not been implemented yet, but it works,  it also omits a crucial part in the usage.

It shows what I would interpret this usage: INSERT INTO gis.test_line (k_id, pg_line) VALUES (1, line((10,10),(20,20)));

 

Where in fact, in the types that I could get it to work I needed the following: INSERT INTO gis.test_line (k_id, pg_line) VALUES (1, line(point(10,10),point(20,20)));

 

Some variations I tried for path and polygon:

 

create table gis.test_path (k_id integer, pg_path path);

INSERT INTO gis.test_path (k_id, pg_path) VALUES (1, path((10,10),(10,20),(20,20), (20,10)));

 

create table gis.test_path2 (k_id integer, pg_path path);

INSERT INTO gis.test_path2 (k_id, pg_path) VALUES (1, path(point(10,10),point(10,20)));

 

create table gis.test_polygon (k_id integer, pg_poly polygon);

INSERT INTO gis.test_polygon (k_id, pg_poly) VALUES (1, polygon((10,10),(10,20),(20,20),(20,10)));

 

create table gis.test_polygon2 (k_id integer, pg_poly polygon);

INSERT INTO gis.test_polygon2 (k_id, pg_poly) VALUES (1, polygon(point(10,10),point(10,20),point(20,20),point(20,10)));

 

Anyone have any suggestions on these two types?

 

Thank you,

Sincerely,

Kasia


This works for me:

# create table test_path (k_id integer, pg_path path);

# INSERT INTO test_path (k_id, pg_path) VALUES (1, path'((10,10),(10,20),(20,20), (20,10))');
INSERT 0 1

(pg90@[local]:5900) 16:37:21 [random] 
# select * from test_path
random-# ;
 k_id |              pg_path              
------+-----------------------------------
    1 | ((10,10),(10,20),(20,20),(20,10))
(1 row)


# create table test_polygon (k_id integer, pg_poly polygon);
CREATE TABLE
# INSERT INTO test_polygon (k_id, pg_poly) VALUES (1, polygon'((10,10),(10,20),(20,20),(20,10))');
INSERT 0 1
(pg90@[local]:5900) 16:41:13 [random] 
# select * from test_polygon;
 k_id |              pg_poly              
------+-----------------------------------
    1 | ((10,10),(10,20),(20,20),(20,10))
(1 row)


Good examples of creating all geometry types could be found here: http://www.postgresql.org/docs/9.0/interactive/functions-geometry.html


regards
Szymon

Re: Postgres native geometry types

От
Kasia Tuszynska
Дата:

Thanks, Szymon the doc reference was the piece of info I was looking for.

Your previous post was a differentiation between the postgis geometry type and the postgis geography type, yes one is planar and the other geodetic but they are both included in PostGIS 1.5

The types in the pg doc are native to postgres, the are quite different from the type available from PostGis.

Does anyone know if they have a usage that can not be fulfilled by PostGis?

Sincerely,

Kasia

 

From: Szymon Guz [mailto:mabewlun@gmail.com]
Sent: Saturday, March 05, 2011 7:53 AM
To: Kasia Tuszynska
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgres native geometry types

 

 

On 4 March 2011 22:58, Kasia Tuszynska <ktuszynska@esri.com> wrote:

Hi Everybody,

 

I am doing some testing on the postgres native geometry types, namely: point, line, lseg, box, path (closed), path [open], polygon and circle.

 

Does anyone know what are they being used for? PostGIS is usually designated as the gis “module” to postgres and there are other spatial types that work on postgres.

So, what are the native geometry types on postgres used for? http://www.postgresql.org/docs/9.0/static/datatype-geometric.html#AEN6332

 

I got the point, line, lseg, box and circle to wok but I am having some problems creating data with the path and polygon types. I am guessing that the postgres doc has not been updated lately. It says that the line function has not been implemented yet, but it works,  it also omits a crucial part in the usage.

It shows what I would interpret this usage: INSERT INTO gis.test_line (k_id, pg_line) VALUES (1, line((10,10),(20,20)));

 

Where in fact, in the types that I could get it to work I needed the following: INSERT INTO gis.test_line (k_id, pg_line) VALUES (1, line(point(10,10),point(20,20)));

 

Some variations I tried for path and polygon:

 

create table gis.test_path (k_id integer, pg_path path);

INSERT INTO gis.test_path (k_id, pg_path) VALUES (1, path((10,10),(10,20),(20,20), (20,10)));

 

create table gis.test_path2 (k_id integer, pg_path path);

INSERT INTO gis.test_path2 (k_id, pg_path) VALUES (1, path(point(10,10),point(10,20)));

 

create table gis.test_polygon (k_id integer, pg_poly polygon);

INSERT INTO gis.test_polygon (k_id, pg_poly) VALUES (1, polygon((10,10),(10,20),(20,20),(20,10)));

 

create table gis.test_polygon2 (k_id integer, pg_poly polygon);

INSERT INTO gis.test_polygon2 (k_id, pg_poly) VALUES (1, polygon(point(10,10),point(10,20),point(20,20),point(20,10)));

 

Anyone have any suggestions on these two types?

 

Thank you,

Sincerely,

Kasia


This works for me:

 

# create table test_path (k_id integer, pg_path path);

 

# INSERT INTO test_path (k_id, pg_path) VALUES (1, path'((10,10),(10,20),(20,20), (20,10))');

INSERT 0 1

 

(pg90@[local]:5900) 16:37:21 [random] 

# select * from test_path

random-# ;

 k_id |              pg_path              

------+-----------------------------------

    1 | ((10,10),(10,20),(20,20),(20,10))

(1 row)

 

 

# create table test_polygon (k_id integer, pg_poly polygon);

CREATE TABLE

# INSERT INTO test_polygon (k_id, pg_poly) VALUES (1, polygon'((10,10),(10,20),(20,20),(20,10))');

INSERT 0 1

(pg90@[local]:5900) 16:41:13 [random] 

# select * from test_polygon;

 k_id |              pg_poly              

------+-----------------------------------

    1 | ((10,10),(10,20),(20,20),(20,10))

(1 row)

 

 

Good examples of creating all geometry types could be found here: http://www.postgresql.org/docs/9.0/interactive/functions-geometry.html

 

 

regards

Szymon