[postgis-users] PostgreSQL geometric data types vs GEOMETRY data type

[Peter Len]

I am using PostgreSQL 9.1 with PostGIS. I am still doing a lot of documentation reading but have the following question.... PostgreSQL comes with some default geometric datatypes like POINT and POLYGON. I created a couple of tables with these types and added some data. After installing PostGIS, I came to find an example of creating a table with a GEOMETRY column. It then went on to show the use of the PostGIS ST_GeomFromText method. That worked fine on my test table that I created with the GEOMETRY(Point) data type but did not work on the table with just the POINT data type. So..... to use the PostGIS methods/functions, do the spatial columns need to be defined as GEOMETRY / GEOGRAPHY rather than the PostgreSQL data types of POINT, POLYGON, etc?

Thanks - Peter
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[Bborie Park]

Correct. PostGIS supports PostGIS datatypes.

-bborie

[Baris Ergun]

Hi Peter;

Can u share ur CREATE Table sqls for both  tables u are testing against to be more specific. 

--

Barış

www.linkedin.com/in/barisergun

[Peter Len]

Thanks Baris.... here are the two table definitions:

-- Using default PostgreSQL geometric types
create table gis.cities (
id serial primary key,
name varchar(50),
state varchar(2),
geom point
);

-- Using PostGIS geometry types
create table gis.table_pt (
id serial primary key,
name varchar(50),
state varchar(2),
geom geometry(Point)
);


The select query below only worked when run against the PostGIS geometry
type:

SELECT id, name FROM gis.table_pt WHERE ST_DWithin(
geom,
ST_GeomFromText('POINT(0 0)', 26910),
1000
);





--
View this message in context: http://postgis.17.x6.nabble.com/PostgreSQL-geometric-data-types-vs-GEOMETRY-data-type-tp5005378p5005381.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

[Baris Ergun]

Until today the usual way I create Geometry columns was to specify it as Geometry(point), Geometry(polygon) etc... and Geometry maybe for generic geometry type. I have never tried to create the data type as point. 

Now if u look spatial functions supported by postgis. You will see they use arguments as geometry, geography, raster... etc. But not point or polygon. Thus ur spatial postgis methods will fail with point type. You can still declare a geometry column with specific point type as Geometry(point,<srid>) as I stated above. 

So why is point, polygon type there? That might be some legacy stuff which might be deprecated. I hope somebody enlighten us about this? As this is something I noticed right now regarding ur thread.

--

Barış

www.linkedin.com/in/barisergun

[Paul Norman]

> From: postgis-us...@lists.osgeo.org [mailto:postgis-users-
> bou...@lists.osgeo.org] On Behalf Of Peter Len
> Subject: [postgis-users] PostgreSQL geometric data types vs GEOMETRY
> data type

>
> I am using PostgreSQL 9.1 with PostGIS. I am still doing a lot of
> documentation reading but have the following question.... PostgreSQL
> comes with some default geometric datatypes like POINT and POLYGON. I
> created a couple of tables with these types and added some data. After
> installing PostGIS, I came to find an example of creating a table with a
> GEOMETRY column. It then went on to show the use of the PostGIS
> ST_GeomFromText method. That worked fine on my test table that I
> created with the GEOMETRY(Point) data type but did not work on the table
> with just the POINT data type. So..... to use the PostGIS
> methods/functions, do the spatial columns need to be defined as GEOMETRY
> / GEOGRAPHY rather than the PostgreSQL data types of POINT, POLYGON,
> etc?

PostgreSQL POINT, etc are unrelated to PostGIS data types.

[Peter Len]

Yeah, I am new to PostgreSQL and PostGIS and so first was looking and
playing with the PostgreSQL built-in geometric data types like POINT and
POLYGON. I did not know that PostGIS added its own datatype (Geometry and
Geography). It seems that just using the PostgreSQL data types is fine to
use if you don't need all the extra PostGIS functionality. But for more
robust geospatial functions, adding in PostGIS and using its data types is
the way to go. On top of that, we are looking to use GeoServer and I
believe (although not positive) that the Geoserver data source will deal
with PostGIS. So..... I am now enlightened on the difference. Thanks for
all the feedback!!



--
View this message in context: http://postgis.17.x6.nabble.com/PostgreSQL-geometric-data-types-vs-GEOMETRY-data-type-tp5005378p5005384.html

Sent from the PostGIS - User mailing list archive at Nabble.com.

[Baris Ergun]

Also take a look at this post

http://stackoverflow.com/questions/1023229/spatial-data-in-postgresql

And here are the postgresql spatial methods

http://www.postgresql.org/docs/9.3/static/functions-geometry.html

Postgis provides you more generic and complex way of declaring geospatial objects both geometrically and geographically.

Postgresql looks like more handling operation in Eucledian Geometry. In postgis you are using a GIS which is aware of Srids, 2d 3d and measure geometries, rasters, topologies.....