Creating a table for Polygon values in Postgis and inserting

5
votes

I have the following area "name" and "polygon" values for 10 different areas ('A',50.6373 3.0750,50.6374 3.0750,50.6374 3.0749,50.63 3.07491,50.6373 3.0750)

I want to create a table in postgres DB using POSTGIS

Later, I will have lan and lat values (e.g. 50.5465 3.0121) in a table to compare with the above table and pull out the area name

Can you help me with the code for both creating and inserting the polygon coordinates?

1
postgresqlpostgis

1 Answers

23
votes

I don't have enough reputation to comment you question, there is a link you might find useful: SQL query for point-in-polygon using PostgreSQL

Adding extension for your database

CREATE EXTENSION postgis;

Creating table 

CREATE TABLE areas (
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    polygon GEOMETRY
);

Creating index over polygon field

CREATE INDEX areas_polygon_idx ON areas USING GIST (polygon);

Inserting record

INSERT INTO areas (name, polygon) VALUES (
    'A',
    ST_GeometryFromText('POLYGON((50.6373 3.0750,50.6374 3.0750,50.6374 3.0749,50.63 3.07491,50.6373 3.0750))')
);

Querying

SELECT name FROM areas WHERE ST_Contains(polygon, ST_GeomFromText('POINT(50.637 3.074)'));
 name 
------
(0 rows)

SELECT name FROM areas WHERE ST_Contains(polygon, ST_GeomFromText('POINT(50.63735 3.07495)'));
 name 
------
 A
(1 row)