I'm trying to wrap my head about how postgres spatial stuff works from a SQL point of view. My goal is to be able to insert polygon geometry references as a column onto a table that also includes other information -- geometry name, et cetera.
I've started out by importing shapefiles into a geometry enabled postgres database. The polygon tables have been created off on a different schema; we'll use polygonGeometry as a example. So, public.geometry_columns has references off to polygonGeometry.(table) for each of my inserted polygons.
I then want to create another table, that has an id (serial primary key), a name (character variable), and a reference to the geometry (either polygon, or a refernece to a different key) that I inserted. How do I go about setting this schema up?
I then have another table with a id (serial primary key), lat (real) and long (real). What SQL query would I run to select geometries from the first table by primary key id, combine them with ST_Union, and return points from the second table created with ST_GeomFromText with the lat and long columns for each row that are within the unioned polygon?
Additionally, does anyone know any good references for getting up to speed with the spatial stuff from a schema, design, and usage standpoint assuming comfortable familiarity with SQL?
