[postgis-users] Using asKML() with google maps
I'm more of a DBA than a GIS developer and I hoping someone can help understand a few things? I'm using asKML() to render some polygons in google maps through a network link. It's in the preliminary phases and works ok. I have seen several examples where geoServer is used with google maps to do basically them same thing. What are the pros and cons of using something like geoServer with google maps. Opposed to only using google maps? Eventually I would like to add some tools to interact with the polygon data, is that where the geoServer would help? Thanks in advance, any advice would be helpful... ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Help with a multipolygon filtering function
I want to clean up some multipolygons and remove any polygons over a certain distance by using a postgis function. This is the closest I've come: CREATE OR REPLACE FUNCTION filter_polygons_within(geometry, double precision) RETURNS geometry AS $BODY$ SELECT ST_Multi(ST_Collect(final_geom.poly)) AS filtered_geom FROM ( SELECT DISTINCT a.poly FROM ( SELECT ST_GeometryN(ST_Multi($1), generate_series(1, ST_NumGeometries(ST_Multi($1 AS poly, (SELECT ST_GeometryN(ST_Multi($1), generate_series(1, ST_NumGeometries(ST_Multi($1) as poly_b ) AS a WHERE a.poly != a.poly_b AND ST_DWithin(a.poly, a.poly_b, $1) ) as final_geom $BODY$ LANGUAGE 'sql' IMMUTABLE; However, the where clause isn't working for me - any ideas on how to achieve this? I'm not sure on how to compare all polygons with each other, I know how to get a list of polygons; ST_GeometryN(ST_Multi($1), generate_series(1, ST_NumGeometries(ST_Multi($1 I'm just not sure how to compare each one with another and return the distinct polygon results. Thanks in advance, Ross ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] migrating tables to postgis
Views are fine in Qgis for me, but in my experience you'll need to manually add a record to the GeometryColumns table, and include a suitable unique ID field into the view or Qgis will complain. I think views are what you want based on your description. I think the table structure qualifier was because you said "etc" without giving all fields from each table, and a one-to-many relationship (or other) wasn't all that clear in your first message. My guess. But yeah, it's easier if you give the relationship between tables and a few rows of sample data (in, out) can't hurt in communicating. Finally, Etienne's suggestion was better - my impression (from the PostGIS in Action book which I recommend by the way!) gives the example of ST_point for your purpose and I've had an easier time using it with column names than ST_GeomFromText. Mark On Wed, Jan 19, 2011 at 11:18 AM, John Callahan wrote: > Thanks for your response Rob. Looks good. I'll give this a try. > > I cannot merge everything into one table. Our station inventory table > contains only the basics/metadata about each station. As other projects > arise, they each have their own tables containing data observations for that > project only. There are many project tables with all types of data. > > As an aside, for future questions, what kind of table information would you > (the list) need in order to provide support? Would you need column types > (string, numeric), or some sample data? Thanks. > > > - John > > ** > John Callahan, Research Scientist > Delaware Geological Survey, University of Delaware > URL: http://www.dgs.udel.edu > ** > > > On Wed, Jan 19, 2011 at 10:56 AM, wrote: > >> Hi John: >> >> Given your stated table structure**, a query like the following should >> give you the records you want. >> >> SELECT t2.*, t1.geometry_column --substitute your newly created geometry >> column for "geometry_column" >> FROM table2 t2 LEFT JOIN table1 t1 ON(t2.stationid = t1.stationid); >> >> The "LEFT JOIN" specifies that you want all rows from table2 and just the >> matching rows from table1. >> >> **It's difficult to predict success of any proposed solution without more >> info on the structure and content of your two tables. >> >> Have you considered merging the data into one table, allowing null values >> for the table2 attributes? You would then be able to select specific records >> without performing a join. If you've already created your table1 geometry >> column, you can quickly generate a "master" table or view (don't know if >> QGIS will recognize a view--might have to be a table) by: >> >> CREATE TABLE stations AS --or CREATE VIEW stations AS >> SELECT t2.*, t1.* >> FROM table2 t2 JOIN table1 t1 ON(t2.stationid = t1.stationid); >> >> Hope that helps. >> >> Cheers, >> Rob >> >> -- >> *From:* postgis-users-boun...@postgis.refractions.net [mailto: >> postgis-users-boun...@postgis.refractions.net] *On Behalf Of *John >> Callahan >> *Sent:* Wednesday, January 19, 2011 9:30 AM >> *To:* PostGIS Users Discussion >> *Subject:* Re: [postgis-users] migrating tables to postgis >> >> Thanks Mark. This gives me a direction to go. I have the correct SRS, >> and your other points are valuable. ST_GeomFromText is really what I was >> missing. Thanks. >> >> The fact that I have two data tables to combine, would a View help here? >> Or somehow modify the SELECT statements that would go into the >> ST_GeomFromText function? (the examples only show hard-coded coordinates >> but I'm hoping a SELECT statement can provide input.) >> >> - John >> >> >> >> On Tue, Jan 18, 2011 at 4:20 PM, MarkW wrote: >> >>> Now that you have data in columns in Postgresql, you can use SQL >>> statements to create your spatial data. Here are the steps: >>> 1) identify your SRID/ spatial reference system >>> 2) create geometries by passing your x and y with a spatial ref to the >>> right function, and >>> 3) add a row to the geometry_columns table so that other applications can >>> more easily see the spatial data. >>> >>> 1) It's much easier if you can match your coordinate system to the right >>> UTM srids in the spatial_ref_sys table; see spatialreference.org for >>> help. >>> 2) See this function: >>> http://www.postgis.org/docs/ST_GeomFromText.html >>> (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out >>> ) >>> 3) >>> http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column >>> (but also see the help under 'AddGeometryColumn ( >>> http://postgis.refractions.net/docs/AddGeometryColumn.html) >>> >>> Hope this helps. >>> >>> Mark >>> >>> >>> On Tue, Jan 18, 2011 at 3:37 PM, John Callahan >> > wrote: >>> I apologize for asking what seems like an easy question. I'm really just getting started with PostGIS and not sure which way to go here. I have a two tables in MS Access format. The
Re: [postgis-users] thiessen polygons
I guess tessellation would be more generic, but it doesn't seem to yield more relevant results. Etienne On Wed, Jan 19, 2011 at 11:32 AM, lplateandy wrote: > > Thanks Etienne - that was the one i came across. I was surprised bostongis > didn't come up when i looked initially but i guess Thiessen isn't the term > to use. > > Andy > > > Etienne B. Racine wrote: > > > > Maybe that could help: > > > http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02 > > > > Etienne > > > > On Wed, Jan 19, 2011 at 10:31 AM, lplateandy > > wrote: > > > >> > >> Hi, > >> > >> For anyone else searching for Thiessen, i've found that there is quite a > >> bit > >> of info out there but under "dierichlet" and some under voroni. > >> > >> Thanks > >> > >> Andy > >> > >> > >> lplateandy wrote: > >> > > >> > Hi, > >> > > >> > I can't seem to find a specifically named Thiessen polygon function - > >> is > >> > there one under a less specific name? > >> > > >> > I'm also wondering whether there is an option to include polygons. My > >> > thinking is to actually just treat each polygon as a set of points > >> itself. > >> > > >> > Thanks in advance, hoping i haven't missed anthing too obvious... > >> > > >> > Andy > >> > > >> > >> -- > >> View this message in context: > >> http://old.nabble.com/thiessen-polygons-tp30676214p30710939.html > >> Sent from the PostGIS - User mailing list archive at Nabble.com. > >> > >> ___ > >> postgis-users mailing list > >> postgis-users@postgis.refractions.net > >> http://postgis.refractions.net/mailman/listinfo/postgis-users > >> > > > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > -- > View this message in context: > http://old.nabble.com/thiessen-polygons-tp30676214p30711567.html > Sent from the PostGIS - User mailing list archive at Nabble.com. > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] thiessen polygons
Thanks Etienne - that was the one i came across. I was surprised bostongis didn't come up when i looked initially but i guess Thiessen isn't the term to use. Andy Etienne B. Racine wrote: > > Maybe that could help: > http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02 > > Etienne > > On Wed, Jan 19, 2011 at 10:31 AM, lplateandy > wrote: > >> >> Hi, >> >> For anyone else searching for Thiessen, i've found that there is quite a >> bit >> of info out there but under "dierichlet" and some under voroni. >> >> Thanks >> >> Andy >> >> >> lplateandy wrote: >> > >> > Hi, >> > >> > I can't seem to find a specifically named Thiessen polygon function - >> is >> > there one under a less specific name? >> > >> > I'm also wondering whether there is an option to include polygons. My >> > thinking is to actually just treat each polygon as a set of points >> itself. >> > >> > Thanks in advance, hoping i haven't missed anthing too obvious... >> > >> > Andy >> > >> >> -- >> View this message in context: >> http://old.nabble.com/thiessen-polygons-tp30676214p30710939.html >> Sent from the PostGIS - User mailing list archive at Nabble.com. >> >> ___ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -- View this message in context: http://old.nabble.com/thiessen-polygons-tp30676214p30711567.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Documentation on Applying Patches
On Wed, Jan 19, 2011 at 06:40:10AM -0800, Ian R Tangert wrote: > > Greetings. Can anyone tell me where I can find documentation on how to apply > patches? Environment specific would be a great help (osx 10.6, postgis 1.5, > postgres 8.4) but anything more generic would help as well. $ cd $ patch < postgis-offsetCurve-RafalMagda.patch $ man patch --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] migrating tables to postgis
OpenJump has no problem with Views, I don't know for the others. http://www.postgresonline.com/journal/archives/72-OpenJump-for-PostGIS-Spatial-Ad-Hoc-Queries.html Etienne On Wed, Jan 19, 2011 at 11:18 AM, John Callahan wrote: > Thanks for your response Rob. Looks good. I'll give this a try. > > I cannot merge everything into one table. Our station inventory table > contains only the basics/metadata about each station. As other projects > arise, they each have their own tables containing data observations for that > project only. There are many project tables with all types of data. > > As an aside, for future questions, what kind of table information would you > (the list) need in order to provide support? Would you need column types > (string, numeric), or some sample data? Thanks. > > > - John > > ** > John Callahan, Research Scientist > Delaware Geological Survey, University of Delaware > URL: http://www.dgs.udel.edu > ** > > > On Wed, Jan 19, 2011 at 10:56 AM, wrote: > >> Hi John: >> >> Given your stated table structure**, a query like the following should >> give you the records you want. >> >> SELECT t2.*, t1.geometry_column --substitute your newly created geometry >> column for "geometry_column" >> FROM table2 t2 LEFT JOIN table1 t1 ON(t2.stationid = t1.stationid); >> >> The "LEFT JOIN" specifies that you want all rows from table2 and just the >> matching rows from table1. >> >> **It's difficult to predict success of any proposed solution without more >> info on the structure and content of your two tables. >> >> Have you considered merging the data into one table, allowing null values >> for the table2 attributes? You would then be able to select specific records >> without performing a join. If you've already created your table1 geometry >> column, you can quickly generate a "master" table or view (don't know if >> QGIS will recognize a view--might have to be a table) by: >> >> CREATE TABLE stations AS --or CREATE VIEW stations AS >> SELECT t2.*, t1.* >> FROM table2 t2 JOIN table1 t1 ON(t2.stationid = t1.stationid); >> >> Hope that helps. >> >> Cheers, >> Rob >> >> -- >> *From:* postgis-users-boun...@postgis.refractions.net [mailto: >> postgis-users-boun...@postgis.refractions.net] *On Behalf Of *John >> Callahan >> *Sent:* Wednesday, January 19, 2011 9:30 AM >> *To:* PostGIS Users Discussion >> *Subject:* Re: [postgis-users] migrating tables to postgis >> >> Thanks Mark. This gives me a direction to go. I have the correct SRS, >> and your other points are valuable. ST_GeomFromText is really what I was >> missing. Thanks. >> >> The fact that I have two data tables to combine, would a View help here? >> Or somehow modify the SELECT statements that would go into the >> ST_GeomFromText function? (the examples only show hard-coded coordinates >> but I'm hoping a SELECT statement can provide input.) >> >> - John >> >> >> >> On Tue, Jan 18, 2011 at 4:20 PM, MarkW wrote: >> >>> Now that you have data in columns in Postgresql, you can use SQL >>> statements to create your spatial data. Here are the steps: >>> 1) identify your SRID/ spatial reference system >>> 2) create geometries by passing your x and y with a spatial ref to the >>> right function, and >>> 3) add a row to the geometry_columns table so that other applications can >>> more easily see the spatial data. >>> >>> 1) It's much easier if you can match your coordinate system to the right >>> UTM srids in the spatial_ref_sys table; see spatialreference.org for >>> help. >>> 2) See this function: >>> http://www.postgis.org/docs/ST_GeomFromText.html >>> (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out >>> ) >>> 3) >>> http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column >>> (but also see the help under 'AddGeometryColumn ( >>> http://postgis.refractions.net/docs/AddGeometryColumn.html) >>> >>> Hope this helps. >>> >>> Mark >>> >>> >>> On Tue, Jan 18, 2011 at 3:37 PM, John Callahan >> > wrote: >>> I apologize for asking what seems like an easy question. I'm really just getting started with PostGIS and not sure which way to go here. I have a two tables in MS Access format. They are: Table1: StationID, easting, northing, elevation, etc... Table2: StationID, data values... Table1 is basically an inventory of all our stations. Table2 is a subset that includes only stations we have certain data for. How would I convert these into a point data set (of Table2 stations) in PostGIS? I was able to copy the tables from Access into Postgres. Where would I go from here?Maybe OGR would help going directly from Access (or text exports of Access) into PostGIS? I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum GIS for viewing. Thanks for any guidan
Re: [postgis-users] migrating tables to postgis
Thanks for your response Rob. Looks good. I'll give this a try. I cannot merge everything into one table. Our station inventory table contains only the basics/metadata about each station. As other projects arise, they each have their own tables containing data observations for that project only. There are many project tables with all types of data. As an aside, for future questions, what kind of table information would you (the list) need in order to provide support? Would you need column types (string, numeric), or some sample data? Thanks. - John ** John Callahan, Research Scientist Delaware Geological Survey, University of Delaware URL: http://www.dgs.udel.edu ** On Wed, Jan 19, 2011 at 10:56 AM, wrote: > Hi John: > > Given your stated table structure**, a query like the following should give > you the records you want. > > SELECT t2.*, t1.geometry_column --substitute your newly created geometry > column for "geometry_column" > FROM table2 t2 LEFT JOIN table1 t1 ON(t2.stationid = t1.stationid); > > The "LEFT JOIN" specifies that you want all rows from table2 and just the > matching rows from table1. > > **It's difficult to predict success of any proposed solution without more > info on the structure and content of your two tables. > > Have you considered merging the data into one table, allowing null values > for the table2 attributes? You would then be able to select specific records > without performing a join. If you've already created your table1 geometry > column, you can quickly generate a "master" table or view (don't know if > QGIS will recognize a view--might have to be a table) by: > > CREATE TABLE stations AS --or CREATE VIEW stations AS > SELECT t2.*, t1.* > FROM table2 t2 JOIN table1 t1 ON(t2.stationid = t1.stationid); > > Hope that helps. > > Cheers, > Rob > > -- > *From:* postgis-users-boun...@postgis.refractions.net [mailto: > postgis-users-boun...@postgis.refractions.net] *On Behalf Of *John > Callahan > *Sent:* Wednesday, January 19, 2011 9:30 AM > *To:* PostGIS Users Discussion > *Subject:* Re: [postgis-users] migrating tables to postgis > > Thanks Mark. This gives me a direction to go. I have the correct SRS, and > your other points are valuable. ST_GeomFromText is really what I was > missing. Thanks. > > The fact that I have two data tables to combine, would a View help here? > Or somehow modify the SELECT statements that would go into the > ST_GeomFromText function? (the examples only show hard-coded coordinates > but I'm hoping a SELECT statement can provide input.) > > - John > > > > On Tue, Jan 18, 2011 at 4:20 PM, MarkW wrote: > >> Now that you have data in columns in Postgresql, you can use SQL >> statements to create your spatial data. Here are the steps: >> 1) identify your SRID/ spatial reference system >> 2) create geometries by passing your x and y with a spatial ref to the >> right function, and >> 3) add a row to the geometry_columns table so that other applications can >> more easily see the spatial data. >> >> 1) It's much easier if you can match your coordinate system to the right >> UTM srids in the spatial_ref_sys table; see spatialreference.org for >> help. >> 2) See this function: >> http://www.postgis.org/docs/ST_GeomFromText.html >> (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out ) >> 3) >> http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column >> (but also see the help under 'AddGeometryColumn ( >> http://postgis.refractions.net/docs/AddGeometryColumn.html) >> >> Hope this helps. >> >> Mark >> >> >> On Tue, Jan 18, 2011 at 3:37 PM, John Callahan >> wrote: >> >>> I apologize for asking what seems like an easy question. I'm really >>> just getting started with PostGIS and not sure which way to go here. I >>> have a two tables in MS Access format. They are: >>> >>> Table1: StationID, easting, northing, elevation, etc... >>> Table2: StationID, data values... >>> >>> Table1 is basically an inventory of all our stations. Table2 is a subset >>> that includes only stations we have certain data for. How would I convert >>> these into a point data set (of Table2 stations) in PostGIS? I was able to >>> copy the tables from Access into Postgres. Where would I go from here? >>> Maybe OGR would help going directly from Access (or text exports of Access) >>> into PostGIS? >>> >>> I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum GIS >>> for viewing. Thanks for any guidance. >>> >>> - John >>> >>> ** >>> John Callahan, Research Scientist >>> Delaware Geological Survey, University of Delaware >>> URL: http://www.dgs.udel.edu >>> ** >>> >>> >> ___ >> postgis-users mailing list >> postgis-users@postgis.refractions
Re: [postgis-users] thiessen polygons
Maybe that could help: http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02 Etienne On Wed, Jan 19, 2011 at 10:31 AM, lplateandy wrote: > > Hi, > > For anyone else searching for Thiessen, i've found that there is quite a > bit > of info out there but under "dierichlet" and some under voroni. > > Thanks > > Andy > > > lplateandy wrote: > > > > Hi, > > > > I can't seem to find a specifically named Thiessen polygon function - is > > there one under a less specific name? > > > > I'm also wondering whether there is an option to include polygons. My > > thinking is to actually just treat each polygon as a set of points > itself. > > > > Thanks in advance, hoping i haven't missed anthing too obvious... > > > > Andy > > > > -- > View this message in context: > http://old.nabble.com/thiessen-polygons-tp30676214p30710939.html > Sent from the PostGIS - User mailing list archive at Nabble.com. > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] migrating tables to postgis
Hi John: Given your stated table structure**, a query like the following should give you the records you want. SELECT t2.*, t1.geometry_column --substitute your newly created geometry column for "geometry_column" FROM table2 t2 LEFT JOIN table1 t1 ON(t2.stationid = t1.stationid); The "LEFT JOIN" specifies that you want all rows from table2 and just the matching rows from table1. **It's difficult to predict success of any proposed solution without more info on the structure and content of your two tables. Have you considered merging the data into one table, allowing null values for the table2 attributes? You would then be able to select specific records without performing a join. If you've already created your table1 geometry column, you can quickly generate a "master" table or view (don't know if QGIS will recognize a view--might have to be a table) by: CREATE TABLE stations AS --or CREATE VIEW stations AS SELECT t2.*, t1.* FROM table2 t2 JOIN table1 t1 ON(t2.stationid = t1.stationid); Hope that helps. Cheers, Rob From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of John Callahan Sent: Wednesday, January 19, 2011 9:30 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] migrating tables to postgis Thanks Mark. This gives me a direction to go. I have the correct SRS, and your other points are valuable. ST_GeomFromText is really what I was missing. Thanks. The fact that I have two data tables to combine, would a View help here? Or somehow modify the SELECT statements that would go into the ST_GeomFromText function? (the examples only show hard-coded coordinates but I'm hoping a SELECT statement can provide input.) - John On Tue, Jan 18, 2011 at 4:20 PM, MarkW wrote: Now that you have data in columns in Postgresql, you can use SQL statements to create your spatial data. Here are the steps: 1) identify your SRID/ spatial reference system 2) create geometries by passing your x and y with a spatial ref to the right function, and 3) add a row to the geometry_columns table so that other applications can more easily see the spatial data. 1) It's much easier if you can match your coordinate system to the right UTM srids in the spatial_ref_sys table; see spatialreference.org for help. 2) See this function: http://www.postgis.org/docs/ST_GeomFromText.html (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out ) 3) http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Co lumn (but also see the help under 'AddGeometryColumn (http://postgis.refractions.net/docs/AddGeometryColumn.html) Hope this helps. Mark On Tue, Jan 18, 2011 at 3:37 PM, John Callahan wrote: I apologize for asking what seems like an easy question. I'm really just getting started with PostGIS and not sure which way to go here. I have a two tables in MS Access format. They are: Table1: StationID, easting, northing, elevation, etc... Table2: StationID, data values... Table1 is basically an inventory of all our stations. Table2 is a subset that includes only stations we have certain data for. How would I convert these into a point data set (of Table2 stations) in PostGIS? I was able to copy the tables from Access into Postgres. Where would I go from here?Maybe OGR would help going directly from Access (or text exports of Access) into PostGIS? I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum GIS for viewing. Thanks for any guidance. - John ** John Callahan, Research Scientist Delaware Geological Survey, University of Delaware URL: http://www.dgs.udel.edu ** ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] migrating tables to postgis
Thank you Etienne. That may be the way to go.
The problem is that one table has the necessary attributes and another table
has the x,y coordinates. They need to be separate tables. Also, I'm using
EPSG:26918 (UTM Z18 meters). Basic structures of both tables are:
Table1: StationID, easting, northing, elevation, owner, date (our complete
inventory table)
Table2: StationID, value1, value2, value3, etc... (a data table with
observations)
Table2 gets updated on a more frequent basis, daily to weekly. Table1 only
a few times per year.
Could I add a geom column to Table1 and then create a view that brings in
the data values I want to map? I am not sure if packages such as QGIS,
MapServer, etc.., can read the view as a spatial class. Or maybe create a
third table that brings in fields from Table1 and Table2, and then adds a
geom column?
- John
**
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
**
On Wed, Jan 19, 2011 at 10:28 AM, Etienne Bellemare wrote:
> You should probably use something like this (let's say your coordinates are
> in the x and y columns [also replace and ]):
>
> SELECT AddGeometryColumn(' ','the_geom', ,'POINT', 2);
> UPDATE SET the_geom = ST_SetSRID(ST_MakePoint(x,y), );
> --replace x and y if it's not the good columns
>
> For the view (you haven't provided much information), it depends of your
> table structure, if they are the same, if when you update one you're likely
> to update the other and if they aren't too big, then maybe it should be
> merged in a single table. Otherwise, a view could be usefull if you need to
> view these simultaneously.
>
> Etienne
>
>
> On Wed, Jan 19, 2011 at 9:30 AM, John Callahan wrote:
>
>> Thanks Mark. This gives me a direction to go. I have the correct SRS,
>> and your other points are valuable. ST_GeomFromText is really what I was
>> missing. Thanks.
>>
>> The fact that I have two data tables to combine, would a View help here?
>> Or somehow modify the SELECT statements that would go into the
>> ST_GeomFromText function? (the examples only show hard-coded coordinates
>> but I'm hoping a SELECT statement can provide input.)
>>
>> - John
>>
>>
>>
>> On Tue, Jan 18, 2011 at 4:20 PM, MarkW wrote:
>>
>>> Now that you have data in columns in Postgresql, you can use SQL
>>> statements to create your spatial data. Here are the steps:
>>> 1) identify your SRID/ spatial reference system
>>> 2) create geometries by passing your x and y with a spatial ref to the
>>> right function, and
>>> 3) add a row to the geometry_columns table so that other applications can
>>> more easily see the spatial data.
>>>
>>> 1) It's much easier if you can match your coordinate system to the right
>>> UTM srids in the spatial_ref_sys table; see spatialreference.org for
>>> help.
>>> 2) See this function:
>>> http://www.postgis.org/docs/ST_GeomFromText.html
>>> (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out
>>> )
>>> 3)
>>> http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column
>>> (but also see the help under 'AddGeometryColumn (
>>> http://postgis.refractions.net/docs/AddGeometryColumn.html)
>>>
>>> Hope this helps.
>>>
>>> Mark
>>>
>>>
>>> On Tue, Jan 18, 2011 at 3:37 PM, John Callahan
>>> wrote:
>>>
I apologize for asking what seems like an easy question. I'm really
just getting started with PostGIS and not sure which way to go here. I
have a two tables in MS Access format. They are:
Table1: StationID, easting, northing, elevation, etc...
Table2: StationID, data values...
Table1 is basically an inventory of all our stations. Table2 is a
subset that includes only stations we have certain data for. How would I
convert these into a point data set (of Table2 stations) in PostGIS? I
was
able to copy the tables from Access into Postgres. Where would I go from
here?Maybe OGR would help going directly from Access (or text exports
of
Access) into PostGIS?
I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum
GIS for viewing. Thanks for any guidance.
- John
**
John Callahan, Research Scientist
Delaware Geological Survey, University of Delaware
URL: http://www.dgs.udel.edu
**
>>> ___
>>> postgis-users mailing list
>>> postgis-users@postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
>
Re: [postgis-users] thiessen polygons
Hi, For anyone else searching for Thiessen, i've found that there is quite a bit of info out there but under "dierichlet" and some under voroni. Thanks Andy lplateandy wrote: > > Hi, > > I can't seem to find a specifically named Thiessen polygon function - is > there one under a less specific name? > > I'm also wondering whether there is an option to include polygons. My > thinking is to actually just treat each polygon as a set of points itself. > > Thanks in advance, hoping i haven't missed anthing too obvious... > > Andy > -- View this message in context: http://old.nabble.com/thiessen-polygons-tp30676214p30710939.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] migrating tables to postgis
You should probably use something like this (let's say your coordinates are
in the x and y columns [also replace and ]):
SELECT AddGeometryColumn(' ','the_geom', ,'POINT', 2);
UPDATE SET the_geom = ST_SetSRID(ST_MakePoint(x,y), );
--replace x and y if it's not the good columns
For the view (you haven't provided much information), it depends of your
table structure, if they are the same, if when you update one you're likely
to update the other and if they aren't too big, then maybe it should be
merged in a single table. Otherwise, a view could be usefull if you need to
view these simultaneously.
Etienne
On Wed, Jan 19, 2011 at 9:30 AM, John Callahan wrote:
> Thanks Mark. This gives me a direction to go. I have the correct SRS, and
> your other points are valuable. ST_GeomFromText is really what I was
> missing. Thanks.
>
> The fact that I have two data tables to combine, would a View help here?
> Or somehow modify the SELECT statements that would go into the
> ST_GeomFromText function? (the examples only show hard-coded coordinates
> but I'm hoping a SELECT statement can provide input.)
>
> - John
>
>
>
> On Tue, Jan 18, 2011 at 4:20 PM, MarkW wrote:
>
>> Now that you have data in columns in Postgresql, you can use SQL
>> statements to create your spatial data. Here are the steps:
>> 1) identify your SRID/ spatial reference system
>> 2) create geometries by passing your x and y with a spatial ref to the
>> right function, and
>> 3) add a row to the geometry_columns table so that other applications can
>> more easily see the spatial data.
>>
>> 1) It's much easier if you can match your coordinate system to the right
>> UTM srids in the spatial_ref_sys table; see spatialreference.org for
>> help.
>> 2) See this function:
>> http://www.postgis.org/docs/ST_GeomFromText.html
>> (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out )
>> 3)
>> http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column
>> (but also see the help under 'AddGeometryColumn (
>> http://postgis.refractions.net/docs/AddGeometryColumn.html)
>>
>> Hope this helps.
>>
>> Mark
>>
>>
>> On Tue, Jan 18, 2011 at 3:37 PM, John Callahan wrote:
>>
>>> I apologize for asking what seems like an easy question. I'm really
>>> just getting started with PostGIS and not sure which way to go here. I
>>> have a two tables in MS Access format. They are:
>>>
>>> Table1: StationID, easting, northing, elevation, etc...
>>> Table2: StationID, data values...
>>>
>>> Table1 is basically an inventory of all our stations. Table2 is a subset
>>> that includes only stations we have certain data for. How would I convert
>>> these into a point data set (of Table2 stations) in PostGIS? I was able to
>>> copy the tables from Access into Postgres. Where would I go from here?
>>> Maybe OGR would help going directly from Access (or text exports of Access)
>>> into PostGIS?
>>>
>>> I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum GIS
>>> for viewing. Thanks for any guidance.
>>>
>>> - John
>>>
>>> **
>>> John Callahan, Research Scientist
>>> Delaware Geological Survey, University of Delaware
>>> URL: http://www.dgs.udel.edu
>>> **
>>>
>>>
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Calculation distance between two points
Hi Nicklas,
thanks for your reply.
Your second version with casting to geographic coordinates works fine for
me. :)
I think on my own I'd never found this solution. ;)
Many thanks again!!
Best regards
Eike
Nicklas Avén wrote:
>
> Hallo Eike
>
> The issue here is, latitude-longitude vs longitude-latitude.
>
> In PostGIS the order is longitude latitude
>
>
> So if you reverse the order of the coordinates you will get about 539
> meters.
>
> What is right or wrong depends on which one is longitude and which one
> is latitude.
>
> Another way to write your query in 1.5 is casting to geography
>
> So, to compare the two orderings:
>
> SELECT
> ST_Distance('POINT(52.2789
> 8.04306)'::geography,'POINT(52.2812598 8.036165)'::geography),
> ST_Distance('POINT(8.04306
> 52.2789)'::geography,'POINT(8.036165 52.2812598)'::geography)
>
>
>
>
>
> HTH
> Nicklas
>
>
>
>
> On Wed, 2011-01-19 at 06:40 -0800, iKey wrote:
>> Dear Community,
>>
>> I want to calculate the distance between two points with a postgres
>> query.
>> The point reference system of my DB is epsg 4326. As far as I know I have
>> to
>> transform them, because with
>> WGS84 4326 I can calculate the distance in radian only.
>>
>> This is an example query in that I try to calculate the distance in
>> different ways, but with no luck so far :(
>> SELECT ST_Distance(ST_Transform(ST_GeomFromText('POINT(52.2789
>> 8.04306)',4326),
>> 900913),ST_Transform(ST_GeomFromText('POINT(52.2812598 8.036165)', 4326),
>> 900913))
>> ,ST_Distance_Sphere(ST_GeomFromText('POINT(52.2789
>> 8.04306)',4326), ST_GeomFromText('POINT(52.2812598 8.036165)',
>> 4326))
>> ,ST_Distance_Spheroid(ST_GeomFromText('POINT(52.2789
>> 8.04306)',4326), ST_GeomFromText('POINT(52.2812598 8.036165)',
>> 4326), 'SPHEROID["WGS 84",6378137,298.257223563]')
>> FROM pois
>> LIMIT 1;
>>
>> The results look like this
>> 818.392971975843;
>> 809.44384291019;
>> 805.637737963218
>>
>> When i calculate the distance in GoogleEarth or GoogleMaps I get a result
>> about 538,06m, so I think there is going something wrong in my
>> calculation with postgres.
>>
>> Does anyone has any ideas whats going wrong or what else I can try?
>>
>> Best regards
>> Eike
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
--
View this message in context:
http://old.nabble.com/Calculation-distance-between-two-points-tp30710456p30710748.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Calculation distance between two points
Hallo Eike
The issue here is, latitude-longitude vs longitude-latitude.
In PostGIS the order is longitude latitude
So if you reverse the order of the coordinates you will get about 539
meters.
What is right or wrong depends on which one is longitude and which one
is latitude.
Another way to write your query in 1.5 is casting to geography
So, to compare the two orderings:
SELECT
ST_Distance('POINT(52.2789
8.04306)'::geography,'POINT(52.2812598 8.036165)'::geography),
ST_Distance('POINT(8.04306
52.2789)'::geography,'POINT(8.036165 52.2812598)'::geography)
HTH
Nicklas
On Wed, 2011-01-19 at 06:40 -0800, iKey wrote:
> Dear Community,
>
> I want to calculate the distance between two points with a postgres query.
> The point reference system of my DB is epsg 4326. As far as I know I have to
> transform them, because with
> WGS84 4326 I can calculate the distance in radian only.
>
> This is an example query in that I try to calculate the distance in
> different ways, but with no luck so far :(
> SELECTST_Distance(ST_Transform(ST_GeomFromText('POINT(52.2789
> 8.04306)',4326),
> 900913),ST_Transform(ST_GeomFromText('POINT(52.2812598 8.036165)', 4326),
> 900913))
> ,ST_Distance_Sphere(ST_GeomFromText('POINT(52.2789
> 8.04306)',4326), ST_GeomFromText('POINT(52.2812598 8.036165)',
> 4326))
> ,ST_Distance_Spheroid(ST_GeomFromText('POINT(52.2789
> 8.04306)',4326), ST_GeomFromText('POINT(52.2812598 8.036165)',
> 4326), 'SPHEROID["WGS 84",6378137,298.257223563]')
> FROM pois
> LIMIT 1;
>
> The results look like this
> 818.392971975843;
> 809.44384291019;
> 805.637737963218
>
> When i calculate the distance in GoogleEarth or GoogleMaps I get a result
> about 538,06m, so I think there is going something wrong in my
> calculation with postgres.
>
> Does anyone has any ideas whats going wrong or what else I can try?
>
> Best regards
> Eike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Calculation distance between two points
Dear Community,
I want to calculate the distance between two points with a postgres query.
The point reference system of my DB is epsg 4326. As far as I know I have to
transform them, because with
WGS84 4326 I can calculate the distance in radian only.
This is an example query in that I try to calculate the distance in
different ways, but with no luck so far :(
SELECT ST_Distance(ST_Transform(ST_GeomFromText('POINT(52.2789
8.04306)',4326),
900913),ST_Transform(ST_GeomFromText('POINT(52.2812598 8.036165)', 4326),
900913))
,ST_Distance_Sphere(ST_GeomFromText('POINT(52.2789
8.04306)',4326), ST_GeomFromText('POINT(52.2812598 8.036165)',
4326))
,ST_Distance_Spheroid(ST_GeomFromText('POINT(52.2789
8.04306)',4326), ST_GeomFromText('POINT(52.2812598 8.036165)',
4326), 'SPHEROID["WGS 84",6378137,298.257223563]')
FROM pois
LIMIT 1;
The results look like this
818.392971975843;
809.44384291019;
805.637737963218
When i calculate the distance in GoogleEarth or GoogleMaps I get a result
about 538,06m, so I think there is going something wrong in my
calculation with postgres.
Does anyone has any ideas whats going wrong or what else I can try?
Best regards
Eike
--
View this message in context:
http://old.nabble.com/Calculation-distance-between-two-points-tp30710456p30710456.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Documentation on Applying Patches
Greetings. Can anyone tell me where I can find documentation on how to apply patches? Environment specific would be a great help (osx 10.6, postgis 1.5, postgres 8.4) but anything more generic would help as well. More specifically, I am interested in trying out the patch here: http://trac.osgeo.org/postgis/attachment/ticket/413/postgis-offsetCurve-RafalMagda.patch http://trac.osgeo.org/postgis/attachment/ticket/413/postgis-offsetCurve-RafalMagda.patch but I can't find anything Googling or otherwise on how to implement. Thank you peace ian -- View this message in context: http://old.nabble.com/Documentation-on-Applying-Patches-tp30596208p30596208.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] migrating tables to postgis
Thanks Mark. This gives me a direction to go. I have the correct SRS, and your other points are valuable. ST_GeomFromText is really what I was missing. Thanks. The fact that I have two data tables to combine, would a View help here? Or somehow modify the SELECT statements that would go into the ST_GeomFromText function? (the examples only show hard-coded coordinates but I'm hoping a SELECT statement can provide input.) - John On Tue, Jan 18, 2011 at 4:20 PM, MarkW wrote: > Now that you have data in columns in Postgresql, you can use SQL statements > to create your spatial data. Here are the steps: > 1) identify your SRID/ spatial reference system > 2) create geometries by passing your x and y with a spatial ref to the > right function, and > 3) add a row to the geometry_columns table so that other applications can > more easily see the spatial data. > > 1) It's much easier if you can match your coordinate system to the right > UTM srids in the spatial_ref_sys table; see spatialreference.org for help. > 2) See this function: > http://www.postgis.org/docs/ST_GeomFromText.html > (and to prove it works, the reverse is ST_X or ST_Y) to get X,Y back out ) > 3) > http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column > (but also see the help under 'AddGeometryColumn ( > http://postgis.refractions.net/docs/AddGeometryColumn.html) > > Hope this helps. > > Mark > > > On Tue, Jan 18, 2011 at 3:37 PM, John Callahan wrote: > >> I apologize for asking what seems like an easy question. I'm really just >> getting started with PostGIS and not sure which way to go here. I have a >> two tables in MS Access format. They are: >> >> Table1: StationID, easting, northing, elevation, etc... >> Table2: StationID, data values... >> >> Table1 is basically an inventory of all our stations. Table2 is a subset >> that includes only stations we have certain data for. How would I convert >> these into a point data set (of Table2 stations) in PostGIS? I was able to >> copy the tables from Access into Postgres. Where would I go from here? >> Maybe OGR would help going directly from Access (or text exports of Access) >> into PostGIS? >> >> I am using Postgres 9.0.2/PostGIS 2.0.0 on Windows, and using Quantum GIS >> for viewing. Thanks for any guidance. >> >> - John >> >> ** >> John Callahan, Research Scientist >> Delaware Geological Survey, University of Delaware >> URL: http://www.dgs.udel.edu >> ** >> >> > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Build PostGIS 1.5.2 under Windows
On 19/01/11 13:27, Ilie, Radu wrote: I am not familiar with the MingW builds. If we do use them, will the resulting dll's have any dependencies on any runtime component of MingW? Or can we simply take the resulting dll's (for postgis, geos, proj etc) and copy them to another Windows system that doesn't have any part of MingW installed? Radu You can check yourself with DEPENDS.EXE, but normally the only dependency is the standard MSVCRT.DLL runtime supplied with Windows. Possibly newer versions may have one additional DLL dependency for debug stack handling. HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Build PostGIS 1.5.2 under Windows
I am not familiar with the MingW builds. If we do use them, will the resulting dll's have any dependencies on any runtime component of MingW? Or can we simply take the resulting dll's (for postgis, geos, proj etc) and copy them to another Windows system that doesn't have any part of MingW installed? Radu -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Mark Cave-Ayland Sent: Wednesday, January 19, 2011 6:20 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Build PostGIS 1.5.2 under Windows On 18/01/11 19:39, Ilie, Radu wrote: > Hi, > > We need to build PostGIS 1.5.2 as 32 bit under Windows, using Visual C++. > > We tried to follow the instructions from > http://trac.osgeo.org/postgis/wiki/DevWikiWinVC_15, but the instructions > from there are not complete. We tried to follow the logical path from > the end of the instructions, but we get thousands of compilation errors. > I noticed at http://www.postgis.org/download/windows/experimental.php > that there are binaries available for distribution under Windows. I > imagine the people building them have a build environment for Windows > already set up. Does anybody know the steps followed by the people that > build the Windows binaries? > > Are there any updated instructions available for building PostGIS under > Windows? Even if they are cumbersome, we can follow them. > > Thank you, Sadly MSVC builds under Windows are currently unsupported, however if you do manage to get any further please do update the wiki with your progress. Is there any particular reason that you can't use the MingW builds? ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Problems using ST_Contains to check if a linestring is included in a multilinestring
Hello, I'm pretty new to postgis and mailing lists, so please excuse if I'm breaking any conventions. :) I'm trying to use ST_Contains to check if a certain linestring is included in a multilinestring. This seems to work fine until the linestring grows. This query (http://paste.frubar.net/13291) should imho return true for both calls, but only the first one equals to true. Any hint on what i'm doing wrong? PostgreSQL 9.0, PostGIS v1.5.2-3, running on Windows. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Build PostGIS 1.5.2 under Windows
On 18/01/11 19:39, Ilie, Radu wrote: Hi, We need to build PostGIS 1.5.2 as 32 bit under Windows, using Visual C++. We tried to follow the instructions from http://trac.osgeo.org/postgis/wiki/DevWikiWinVC_15, but the instructions from there are not complete. We tried to follow the logical path from the end of the instructions, but we get thousands of compilation errors. I noticed at http://www.postgis.org/download/windows/experimental.php that there are binaries available for distribution under Windows. I imagine the people building them have a build environment for Windows already set up. Does anybody know the steps followed by the people that build the Windows binaries? Are there any updated instructions available for building PostGIS under Windows? Even if they are cumbersome, we can follow them. Thank you, Sadly MSVC builds under Windows are currently unsupported, however if you do manage to get any further please do update the wiki with your progress. Is there any particular reason that you can't use the MingW builds? ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
