CODEWITHSUNDEEP
sqlpostgresqlpolygonpostgis
liv a
liv a

Reputation: 3350

postgresql with postgis & polygons

I'm working with postgresql with postgis. I have few questions:

  1. trying to insert into a table with polygon column using the following syntax:
ST_GeomFromText('POLYGON((long1 lat1, long2 lat2, long3 lat3))')

fails with the following error: function geomfromtext(unknown) does not exist

  1. what is the difference between 
CREATE TABLE my_table (my_polys polygon);

and

CREATE TABLE my_table2 (my_polys GEOGRAPHY(POLYGON));

and why does the following:

INSERT INTO my_table (my_polys) VALUES ('
    (51.504824, -0.125918),
    (51.504930, -0.122743),
    (51.504930, -0.110297),
    (51.504824, -0.102229),
    (51.503435, -0.099311)'
);

work fine with my_table and not with my_table2 (I've changed the table name to my_table2)

  1. what is the maximum number of points a polygon can have?

Upvotes: 1

Views: 2080

Answers (2)

John Powell
John Powell

Reputation: 12581

The geography data type is mostly useful when your geometry might wrap around the date line. If it does not, you are better off using normal geometry data types, as most functions in Postgis are designed to work on planar geometries. If your data are lat/lon, you can explicitly set this when you create your column with:

create table foo geom geometry(POLYGON, 4326);

Explicitly setting the coordinate reference system is useful if you want to convert between one coordinate system and another and helps prevent suprises if you attempt to run an intersection, say, between geometries in different coordinate systems.

It is hard to imagine your insert fragment above working with any table, not only is there a trailing comma, but there is no st_geomfromtext, st_makepolygon or anything else to convert what you have written into any geometry.

I have no idea what the maximum size for a polygon is, although, as Chris has already said, you are likely to have performance issues with polygons of 1gb in size, when you try and do spatial queries on them.

Upvotes: 1

Chris Travers
Chris Travers

Reputation: 26464

Your polygon size is limited by the max storage size. I believe this is limited to 1gb for a toasted column, though my guess is that you are likely to run into performance issues long before you exceed the maximum number of points.

Polygon is a built in type in PostgreSQL, and PostGIS offers additional types for geometry and geography. As I understand it the built-in-types only work for 2d Euclidean space, while PostGIS offers additional options there.

As for why your query fails, I wonder which version of PostGIS you are running and why st_geomfromtext is calling geomfromtext.

Upvotes: 0

Related Questions