PostgreSQL – How to Create Tables with Geometry

postgispostgresql

I need to create 2 tables with distinct geometry.

One needs to be a table that has point information. The other needs to be a table that has line information.

They do not need to be populated as this is just a test. They just need to have the geometry column.

The code so far is:

CREATE TABLE point

CREATE TABLE line

How to add the geometry into the code?

The simple answer is:

create table points ( p POINT not null); 
create table lines ( l LINE not null);

Then inserts look like this:

insert into points (p) values (POINT(1.2, 123.1));
insert into lines (l) values (LINE(POINT(1.2, 123.1), POINT(-5, -123)));

You probably want to build indexes if you want to have some lookups on them. Also a primary key may be beneficial.

What you want is ST_DWithin. That will use an index.

SELECT ascii_name
FROM geoname
WHERE
ST_DWithin(
  ST_SetSRID(ST_MakePoint(-122.67621 45.52345), 4326),
  the_geom,
  16093
);

You probably want to use geography rather than geometry.

From the PostgreSQL documentation:

CREATE TABLE new_table AS
SELECT * FROM ab1_2;

Replace the * with the field names (with alias if you need to change the name) you need. You can put in the other 5 tables as UNION:

CREATE TABLE new_table AS
SELECT * FROM ab1_2
UNION
SELECT * FROM ab3
UNION
SELECT * FROM ab4
UNION
SELECT * FROM ab5
UNION
SELECT * FROM ab6;

In the second one only the field names are needed. No use to give them an alias.

Or write 5 SQLs in the form:

INSERT INTO new_table
SELECT * FROM other_table;