Spatially Enabling a Table in PostGIS

I recently had a situation where a customer had a table with XY coordinates and I needed to make it spatially aware in PostgreSQL/PostGIS. The moving the data around bit is rather droll so I’ll skip it, but the spatially enabling part you might find useful.

PostGIS comes with a lot of functions to build geometry. Here we’re going to look at a simple point geometry, but if you have the data, it can build line and polygon topology as well.

Let’s say we have a table with X and Y coordinate fields. We’ll call the table mytable, and we’ll call the X and Y fields x_coord and y_coord respectively. Sure, we could come up with more creative names, but it’s early in the morning.

The first thing we need to do is add a geometry column to our table. We’re going to use the AddGeometryColumn function to handle that task, which does two things for us: it adds a geometry column to the table, and it registers the table in the geometry_columns table. The function operates like so:

AddGeometryColumn(<schema_name>, <table_name>, <column_name>, <srid>, <type>, <dimension>)

I always name the geometry column the_geom, because it's the default and I'm not terribly creative. Whatever you name it, you should name the geometry column in every layer in your database the same or your life will become a furious ball of nothing.

So here’s our command:

SELECT AddGeometryColumn( 'public', 'mytable', 'the_geom', 2264, 'POINT', 2 )

Our coordinates in this case are SRID 2264 - you'll want to adjust that to what you have.

We now have a registered geometry column, but there isn’t anything in it. Now we’ll to populate the geometry column from are X and Y coordinate fields with the PointFromText function:

UPDATE mytable SET the_geom = PointFromText('POINT(' || x_coordinate || ' ' || y_coordinate || ')', 2264)

Almost done. All we need to do now is add an index to the geometry column to make things head-slapping fast. We create the index like any other PostgreSQL index, but we use the GIST index type with GIST_GEOMETRY_OPS:

CREATE INDEX idx_mytable_geo on mytable USING GIST(the_geom GIST_GEOMETRY_OPS)

That's it. To make our XY table a spatial layer in PostgreSQL, we just run three SQL commands:

SELECT AddGeometryColumn( 'public', 'mytable', 'the_geom', 2264, 'POINT', 2 ); UPDATE mytable SET the_geom = PointFromText('POINT(' || x_coordinate || ' ' || y_coordinate || ')', 2264); CREATE INDEX idx_mytable_geo on mytable USING GIST(the_geom GIST_GEOMETRY_OPS)

If you want to get rid of the geometry column for some reason, just run this:

SELECT DropGeometryColumn('public', 'mytable', 'the_geom')

That will remove the geometry column from the table and remove the entry from the geometry_columns table.

That’s all there is to it. Making a point layer from a table with XY coordinates couldn’t be easier.