[postgis-users] (no subject)
http://www.gordongatherer.com/find11.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Announcing GEOS-3.3.0 and roadmap to 3.4.0
GEOS 3.3.0 is out: http://download.osgeo.org/geos/geos-3.3.0.tar.bz2 This release introduces a fair amount of new C-API interfaces and a brand new PHP binding. Full details in the NEWS file: http://trac.osgeo.org/geos/browser/tags/3.3.0/NEWS As with any release since 3.0.0 there is complete binary compatibility with clients linked against the C-API. These include, but are not limited to, PostGIS. For a list of known clients: http://trac.osgeo.org/geos/wiki/Applications (add yours, if not already listed!) GEOS is a C++ port of the JTS Topology Suite. This release targets version 1.12 of the library, but doesn't reach full feature parity yet. Missing JTS functionalities: From JTS-1.11 - Densifier class - 'match' package, for geometric similarity detection (HausdorffSimilarityMeasure, AreaSimilarityMeasure) - MinimumDiameter.getMiminumRectangle() - Triangulation API - VoronoiDiagramBuilder - createSquircle and createSuperCircle in GeometricShapeFactory From JTS-1.12 - MinimumClearance class - nearestNeighbours method to STRtree - RandomPointsBuilder / RandomPointsInGridBuilder - KochSnowflakeBuilder - SierpinksiCarpetBuilder If you'd like to sponsor development of any of the above items (or others) for next feature release of GEOS (3.4.0) please drop me a note. --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] Fw: re: Geocoder (from extras)
Right, This is happening in a stored procedure that is doing some additional work, but my point was, that the in the 200-400ms case you are measuring the the disk hit performance as a page is getting located on disk and paged into cache and at the faster 20-40ms you are getting just the index speed, the page look up in memory and record access and whatever additional execution I'm doing in the stored procedure. The 20-40ms performance is on average when processing a few 100,000 requests randomized over the coverage area so the actual faster speeds are probably closer to 5-8ms and average out to the 20-40ms when you cost in the out of cache page hits. Sorry for provide less than complete information originally. -Steve On 5/29/2011 1:14 PM, Johnathan Leppert wrote: 30 million records isn't really a lot with an index and shouldn't be taking quite that long? Johnathan On May 29, 2011, at 12:28 AM, Stephen Woodbridgewood...@swoodbridge.com wrote: I have had similar performance experiences working with tiger data in other applications, ie not this geocoder, where queries cost about 200-400ms initially querying a 30 million record streets table and then go to 20-40ms afterwards. I have always attributed this to page caching. My queries are typically spatial in nature and I cluster my data based on the spatial index. But for the geocoder, I would expect similar performance if you cluster your data by zipcode and then sort your input data by zipcode, you should get very good performance depending on your queries and indexes. -Steve On 5/28/2011 5:24 PM, Paragon Corporation wrote: Mikal, Can you send me the change you made and the indexes you added. When adding some more data, I realized I had hardcoded an index for our local state (MA) and I know without that index that that particular query does run pretty slow. So just wondering if its along the same lines. I've also fixed I think all the issues with running the loader on Unix/Linux -- well at least I was able to get it to run on my CentOS. Thanks to all who contributed input to that. I took bits and pieces from many people's comments but couldn't apply a full diff from anyones since I had already changed the code too much to safely apply any of those patches. How many states do you have loaded BTW? I just have CA loaded on my CentOS -- which is an 8GB/8 core cloud server. I'm getting around 38ms - 450ms per test, but I have yet to load the other states. It also seems to cache very well so that if I geocode an address on the same street (not necessarily same address), the first call might take 450ms and the second 38ms. I suspect this might be because I also marked a good chunk of the functions STABLE or IMMUTABLE. Thanks, Regina http://www.postgis.us *From:* postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Mikal Laster *Sent:* Friday, May 27, 2011 8:18 AM *To:* postgis-users@postgis.refractions.net *Subject:* [postgis-users] Fw: re: Geocoder (from extras) --- On *Fri, 5/27/11, Mikal Laster /orcl...@yahoo.com/* wrote: From: Mikal Lasterorcl...@yahoo.com Subject: re:[postgis-users] Geocoder (from extras) To: postgis-users@postgis.refractions.net Date: Friday, May 27, 2011, 7:22 AM in response to http://postgis.refractions.net/pipermail/postgis-users/2011-May/029566.html. After creating some indexes and rewriting geocode_address. I was able to get geocode to run in 483-523 ms for 5775 Perimeter Dr Dublin, Ohio. This used to take 1700-2000 ms for me. I'm removing the main inner qui ___ 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Google Maps API with PostGIS
Hello all, I am fairly new to PostGIS and I have to work on a project for my PhD. I hope you can help me. I want to build a web map where the user can select a building or more than one from a layer on top of Google Maps API and the code should create an automatic convex hull from the set of points generated from the selected polygon(s). I am not sure on what to use but I have general ideas and I do not know which is the best : - Do I use Google Maps API and PostGIS without needing a WMS or WFS? - Do I have to use with Google Maps API a WMS or a WFS? And if someone has beneficial samples I can use to build the website, I would really appreciate it. Thank you in advance. *--- Ahmad Aburizaiza PhD student at **George Mason University** Geography and Geoinformation Science Department Fairfax, VA, USA Tel : +1-703-981-0354* ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Google Maps API with PostGIS
On 30/05/2011 18:51, Ahmad Aburizaiza wrote: Perhaps you need to look at something like mapserver or geoserver Both of these application are used as part of a web server either tomcat or apache. The will connect to postgis and provide access to your table as wms/wfs server. You can use something like openlayers to provide any pretty front end/ drawing of shapes etc. D. Hello all, I am fairly new to PostGIS and I have to work on a project for my PhD. I hope you can help me. I want to build a web map where the user can select a building or more than one from a layer on top of Google Maps API and the code should create an automatic convex hull from the set of points generated from the selected polygon(s). I am not sure on what to use but I have general ideas and I do not know which is the best : * Do I use Google Maps API and PostGIS without needing a WMS or WFS? * Do I have to use with Google Maps API a WMS or a WFS? And if someone has beneficial samples I can use to build the website, I would really appreciate it. Thank you in advance. *--- Ahmad Aburizaiza PhD student at **George Mason University** Geography and Geoinformation ScienceDepartment Fairfax, VA, USA Tel : +1-703-981-0354 tel:703-981-0354* ___ 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] Enter details into Geom column
Dear Brent,
Thank you for your reply and simple explanation, it's much appreciated.
Unfortunately, it doesn't seem to work. When I try to create the Geom
column, I get this error:
-
ERROR: function st_creategeometrycolumn(unknown, unknown, unknown, integer,
unknown, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Character: 8
-
So I browsed the PostGIS functions, and thought that I should perhaps use
the function 'AddGeometryColumn' instead, so changed the code to below:
select AddGeometryColumn('public',
'CLEANEDCAMDENGPS','geom',4326,'POINT',2);
However this returns an error of the below:
ERROR: relation public.CLEANEDCAMDENGPS does not exist
I should perhaps say at this point, that the table called CLEANEDCAMDENGPS
is within a database called CAMDENGPS. I played around with trying to put
the database name into the statement too, but with no luck.
Any thoughts?
Thanks again, and yes, I'll take onboard your point about captials and table
names from this point forwards.
Cheers
James
On 29 May 2011 23:44, pcr...@pcreso.com wrote:
Hi James,
I suggest you avoid upper case letters in table column names if you can.
It makes a few things easier
The syntax in both SQL statements is wrong. Try:
select
ST_CreateGeometryColumn('public','CLEANEDCAMDENGPS','geom',4326,'POINT',2);
the fields are (in order):
schema where table can be found ('public')
the table name where you want the new column ('CLEANEDCAMDENGPS')
the name of the geometry column to create ('geom')
the SRID of the geometry column to create (4326)
the geometry type ('POINT')
the number of dimensions (2 - x y)
All string values need to be quoted.
To populate this column try:
update CLEANEDCAMDENGPS
set geom=setsrid(makepoint(LONGITUDE,LATITUDE),4326);
So, create a point geometry from the two numeric columns (makepoint), force
the SRID of this geometry to 4326 (setsrid), write this value to your new
column (update table set column =).
HTH,
Brent Wood
James Smith wrote:
Dear all,
Would appreciate some help. I have created an existing database (with
PostGIS extension) and it has a table called CLEANEDCAMDENGPS which
is populated with approx 600,000 rows. There are 20 or so columns in the
table, two of which are Latitude and Longitude (WGS84). I would now
like to create a Geom column with points in, the values of which
should be taken from the latitude and longitude column. Could someone
provide me with sample code as to how to do this please? I had a go
with the below, but don't really know what I'm doing... neither of the
statements work...
--CREATE THE COLUMN--
SELECT AddGeometryColumn('CAMDENGPS', 'GPS_POINTS', 'geom', 4326,
'POINT', 2)
--POPULATE THE COLUMN--
INSERT INTO CLEANEDCAMDENGPS (GPS_POINTS)
VALUES ( ST_GEOMFROMTEXT('POINT((SELECT LONGITUDE FROM
CLEANEDCAMDENGPS) (SELECT LATITUDE FROM CLEANEDCAMDENGPS))', 4326,
'Point'));
Thank you
James
___
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] Google Maps API with PostGIS
In addition, rather than using WFS, you could hit the database directly using something like a PHP script. On Mon, May 30, 2011 at 2:33 PM, Dave dave.po...@pinan.co.uk wrote: On 30/05/2011 18:51, Ahmad Aburizaiza wrote: Perhaps you need to look at something like mapserver or geoserver Both of these application are used as part of a web server either tomcat or apache. The will connect to postgis and provide access to your table as wms/wfs server. You can use something like openlayers to provide any pretty front end/ drawing of shapes etc. D. Hello all, I am fairly new to PostGIS and I have to work on a project for my PhD. I hope you can help me. I want to build a web map where the user can select a building or more than one from a layer on top of Google Maps API and the code should create an automatic convex hull from the set of points generated from the selected polygon(s). I am not sure on what to use but I have general ideas and I do not know which is the best : - Do I use Google Maps API and PostGIS without needing a WMS or WFS? - Do I have to use with Google Maps API a WMS or a WFS? And if someone has beneficial samples I can use to build the website, I would really appreciate it. Thank you in advance. * --- Ahmad Aburizaiza PhD student at **George Mason University** Geography and Geoinformation Science Department Fairfax, VA, USA Tel : +1-703-981-0354* ___ postgis-users mailing listpostgis-users@postgis.refractions.nethttp://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] Google Maps API with PostGIS
On 5/30/2011 1:51 PM, Ahmad Aburizaiza wrote: Hello all, I am fairly new to PostGIS and I have to work on a project for my PhD. I hope you can help me. I want to build a web map where the user can select a building or more than one from a layer on top of Google Maps API and the code should create an automatic convex hull from the set of points generated from the selected polygon(s). I am not sure on what to use but I have general ideas and I do not know which is the best : * Do I use Google Maps API and PostGIS without needing a WMS or WFS? * Do I have to use with Google Maps API a WMS or a WFS? And if someone has beneficial samples I can use to build the website, I would really appreciate it. Thank you in advance. I think you need to think through the whole flow of this application and answer questions like: 1. the user selects a polygon(s) how does this happen? where are the polygons? in the client, or on the your server, or on google's server? How did they get display on the page because this will determine what the selection process needs to be. 2. create a convex hull from the select polygons if you are creating the convex hull in postgis, then you need to get the selections to your postgis server. where is the postgis server? Where are the selected polygons? 3. now you have a convex hull what do you want to do with it? display it on the map? do you want to draw it as a vector object or draw it as an image overlay? Do you need to store the convex hull for later use? Where do you plan to store it? As you know there are many ways to skin a cat. Without more information on your specific use case, I would implement this using OpenLayers and not the Google API (in part because it open javascript api for mapping and feature manipulation and I know it better than the Google API), and I would probably write a simple PHP ajax handler to interact between the web application and the database. I assume the building are already loaded in postgis. If you need more then say 500 building display on the screen them you will probably want to draw them via mapserver as an image overlay, but if you have less then passing the features to OpenLayers and rendering them as a vector layer is probably fine. Hope this helps, -Steve W ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Enter details into Geom column
If you have a table named CLEANEDCAMDENGPS (in public), and you want
a column GPS_POINTS:
SELECT AddGeometryColumn('CLEANEDCAMDENGPS', 'GPS_POINTS', 4326, 'POINT', 2);
works fine. There is some confusion in your first message with another
table CAMDENGPS and another geometry column geom. Also, just be
warned that although you can name tables/columns with mixed and upper
case, I've found it to be more trouble than it is worth. For instance,
you will always need to use double quotes for these entities:
SELECT ST_X(GPS_POINTS), ST_Y(GPS_POINTS)
FROM CLEANEDCAMDENGPS
LIMIT 10;
You can easily rename them in pgAdmin to a lowercase equivalent, which
doesn't require quoting.
-Mike
On 31 May 2011 06:35, James Smith james.david.sm...@gmail.com wrote:
Dear Brent,
Thank you for your reply and simple explanation, it's much appreciated.
Unfortunately, it doesn't seem to work. When I try to create the Geom column,
I get this error:
-
ERROR: function st_creategeometrycolumn(unknown, unknown, unknown, integer,
unknown, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Character: 8
-
So I browsed the PostGIS functions, and thought that I should perhaps use the
function 'AddGeometryColumn' instead, so changed the code to below:
select AddGeometryColumn('public', 'CLEANEDCAMDENGPS','geom',4326,'POINT',2);
However this returns an error of the below:
ERROR: relation public.CLEANEDCAMDENGPS does not exist
I should perhaps say at this point, that the table called CLEANEDCAMDENGPS is
within a database called CAMDENGPS. I played around with trying to put the
database name into the statement too, but with no luck.
Any thoughts?
Thanks again, and yes, I'll take onboard your point about captials and table
names from this point forwards.
Cheers
James
On 29 May 2011 23:44, pcr...@pcreso.com wrote:
Hi James,
I suggest you avoid upper case letters in table column names if you can.
It makes a few things easier
The syntax in both SQL statements is wrong. Try:
select
ST_CreateGeometryColumn('public','CLEANEDCAMDENGPS','geom',4326,'POINT',2);
the fields are (in order):
schema where table can be found ('public')
the table name where you want the new column ('CLEANEDCAMDENGPS')
the name of the geometry column to create ('geom')
the SRID of the geometry column to create (4326)
the geometry type ('POINT')
the number of dimensions (2 - x y)
All string values need to be quoted.
To populate this column try:
update CLEANEDCAMDENGPS
set geom=setsrid(makepoint(LONGITUDE,LATITUDE),4326);
So, create a point geometry from the two numeric columns (makepoint), force
the SRID of this geometry to 4326 (setsrid), write this value to your new
column (update table set column =).
HTH,
Brent Wood
James Smith wrote:
Dear all,
Would appreciate some help. I have created an existing database (with
PostGIS extension) and it has a table called CLEANEDCAMDENGPS which
is populated with approx 600,000 rows. There are 20 or so columns in the
table, two of which are Latitude and Longitude (WGS84). I would now
like to create a Geom column with points in, the values of which
should be taken from the latitude and longitude column. Could someone
provide me with sample code as to how to do this please? I had a go
with the below, but don't really know what I'm doing... neither of the
statements work...
--CREATE THE COLUMN--
SELECT AddGeometryColumn('CAMDENGPS', 'GPS_POINTS', 'geom', 4326, 'POINT',
2)
--POPULATE THE COLUMN--
INSERT INTO CLEANEDCAMDENGPS (GPS_POINTS)
VALUES ( ST_GEOMFROMTEXT('POINT((SELECT LONGITUDE FROM
CLEANEDCAMDENGPS) (SELECT LATITUDE FROM CLEANEDCAMDENGPS))', 4326,
'Point'));
Thank you
James
___
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
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Google Maps API with PostGIS
Hi, 2011/5/30 Stephen Woodbridge wood...@swoodbridge.com wrote: As you know there are many ways to skin a cat. Without more information on your specific use case, I would implement this using OpenLayers and not the Google API (in part because it open javascript api for mapping and feature manipulation and I know it better than the Google API), and I would probably write a simple PHP ajax handler to interact between the web application and the database. I assume the building are already loaded in postgis. If you need more then say 500 building display on the screen them you will probably want to draw them via mapserver as an image overlay, but if you have less then passing the features to OpenLayers and rendering them as a vector layer is probably fine. Here's something I did in Ajax/PHP to play around: The PostGIS Terminal - http://152.96.80.16/ Try following query in order to get a convex hull around all Nelson pubs in Switzerland (from OpenStreetMap): SELECT ST_AsText(ST_ConvexHull(ST_Collect(way))) FROM osm_point WHERE amenity='pub' AND name ILIKE 'Nelson%' Yours, Stefan ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] st_difference() problem?
Hmm. Blush. Now that I have posted and annoyed you all, I believe I see my
problem - I'm getting an outer product effect from my st_difference as it
visits each polygon.
So I need to go away and think about the join condition on those queries.
Sorry...
On Mon, May 30, 2011 at 5:06 PM, Chris Hermansen
chris.herman...@tecogroup.ca wrote:
Ladies and gentlemen;
Nice to be back.
I am wrestling with st_difference() and it is getting the better of me. I
am hoping someone who has seen this before can help.
I have two tables that are buffer polygons, built by PostGIS, around some
linear features (trails and traplines).
I am seeking to combine these two tables to produce a single set of
polygons that shows areas that are inside the trail buffers, inside the
trapline buffers, both, or neither.
The pattern I am following is
st_difference(btrail, btrapline)
union
st_intersection(btrail, btrapline)
union
st_difference(btrapline, btrail)
The btrail table has 9 rows, all valid POLYGONs; the btrapline table has 18
rows, all valid POLYGONs. Nothing self-overlaps. All have SRID of 3005.
There is a place in the southwest of the area where one trapline buffer and
one trail buffer cross over each other twice. No other features come close.
If I copy just those two polygons into two separate test tables and use my
difference/intersection/difference operation, I get the result I expect - 10
polygons, none overlapping, where some are trapline-only, some are
trail-only, and some are both trail and trapline. A visual inspection of
the result in QGIS makes sense.
If I perform the same analysis on the full data set, I get a result that
seems wrong - looking at the portion of the data related to those two
features, instead of 10 polygons, I see 35 polygons. Many of these result
polygons overlap each other. Visually (in QGIS again), I see what appears
to be the two original input polygons covering a bunch of other polygons.
I can of course post the data; I also have a few screen shots from QGIS
saved as jpg files.
This is from PostgreSQL 8.4.8 running on Ubuntu 10.10. The output from
postgis_full_version() is
POSTGIS=1.5.1 GEOS=3.2.0-CAPI-1.6.0 PROJ=Rel. 4.7.1, 23 September
2009 LIBXML=2.7.6 USE_STATS
This is the code that produces the multiple overlapping result polygons
with the full data set and seemingly normal results with the two test
buffers.
-- create the diff/int/diff on the full table
drop table utrailtrapline;
create table utrailtrapline (
gid serial,
istrail boolean,
istrapline boolean);
SELECT
AddGeometryColumn('','utrailtrapline','the_geom','3005','POLYGON',2);
insert into utrailtrapline (istrail, istrapline, the_geom)
select
istrail,
FALSE as istrapline,
(st_dump(st_difference(btrail.the_geom, btrapline.the_geom))).geom as
the_geom
from btrail, btrapline;
insert into utrailtrapline (istrail, istrapline, the_geom)
select
istrail,
istrapline,
(st_dump(st_intersection(btrail.the_geom, btrapline.the_geom))).geom as
the_geom
from btrail, btrapline
where st_intersects(btrail.the_geom, btrapline.the_geom);
insert into utrailtrapline (istrail, istrapline, the_geom)
select
FALSE as istrail,
istrapline,
(st_dump(st_difference(btrapline.the_geom, btrail.the_geom))).geom as
the_geom
from btrail, btrapline;
Thanks in advance!
--
Chris Hermansen
TECO Natural Resource Group Limited
301 · 958 West 8th Avenue
Vancouver BC CANADA · V5Z 1E5
Tel +1.604.714.2878 · Cel +1.778.840.4625
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Enter details into Geom column
James,
The error message has two possibilities:
ERROR: relation public.CLEANEDCAMDENGPS does not exist
either their is no table called 'CLEANEDCAMDENGPS' (and I think you've probably
got the idea about the issues with case), but it is also possible that the
schema is not 'public', depending on your access to a database. If it is on
your local computer, and you have no idea what I'm talking about, then it
probably is public. If you are using a shared installation, it may not be
public.
if you are using psql (the command line), and try a command like \dt, you will
see a list of the 'relations' which do exist - something like
List of relations
Schema | Name | Type | Owner
+--+---+---
backoffice | access | table | ben
backoffice | accesslevel | table | ben
backoffice | categories | table | ben
if your table is not in this list, (it's specified as backoffice.access for
instance) then it may not exist, or you need to look up search_path.
cheers
Ben
On 31/05/2011, at 2:35 AM, James Smith wrote:
Dear Brent,
Thank you for your reply and simple explanation, it's much appreciated.
Unfortunately, it doesn't seem to work. When I try to create the Geom column,
I get this error:
-
ERROR: function st_creategeometrycolumn(unknown, unknown, unknown, integer,
unknown, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Character: 8
-
So I browsed the PostGIS functions, and thought that I should perhaps use the
function 'AddGeometryColumn' instead, so changed the code to below:
select AddGeometryColumn('public', 'CLEANEDCAMDENGPS','geom',4326,'POINT',2);
However this returns an error of the below:
ERROR: relation public.CLEANEDCAMDENGPS does not exist
I should perhaps say at this point, that the table called CLEANEDCAMDENGPS is
within a database called CAMDENGPS. I played around with trying to put the
database name into the statement too, but with no luck.
Any thoughts?
Thanks again, and yes, I'll take onboard your point about captials and table
names from this point forwards.
Cheers
James
On 29 May 2011 23:44, pcr...@pcreso.com wrote:
Hi James,
I suggest you avoid upper case letters in table column names if you can. It
makes a few things easier
The syntax in both SQL statements is wrong. Try:
select
ST_CreateGeometryColumn('public','CLEANEDCAMDENGPS','geom',4326,'POINT',2);
the fields are (in order):
schema where table can be found ('public')
the table name where you want the new column ('CLEANEDCAMDENGPS')
the name of the geometry column to create ('geom')
the SRID of the geometry column to create (4326)
the geometry type ('POINT')
the number of dimensions (2 - x y)
All string values need to be quoted.
To populate this column try:
update CLEANEDCAMDENGPS
set geom=setsrid(makepoint(LONGITUDE,LATITUDE),4326);
So, create a point geometry from the two numeric columns (makepoint), force
the SRID of this geometry to 4326 (setsrid), write this value to your new
column (update table set column =).
HTH,
Brent Wood
James Smith wrote:
Dear all,
Would appreciate some help. I have created an existing database (with
PostGIS extension) and it has a table called CLEANEDCAMDENGPS which
is populated with approx 600,000 rows. There are 20 or so columns in the
table, two of which are Latitude and Longitude (WGS84). I would now
like to create a Geom column with points in, the values of which
should be taken from the latitude and longitude column. Could someone
provide me with sample code as to how to do this please? I had a go
with the below, but don't really know what I'm doing... neither of the
statements work...
--CREATE THE COLUMN--
SELECT AddGeometryColumn('CAMDENGPS', 'GPS_POINTS', 'geom', 4326, 'POINT',
2)
--POPULATE THE COLUMN--
INSERT INTO CLEANEDCAMDENGPS (GPS_POINTS)
VALUES ( ST_GEOMFROMTEXT('POINT((SELECT LONGITUDE FROM
CLEANEDCAMDENGPS) (SELECT LATITUDE FROM CLEANEDCAMDENGPS))', 4326,
'Point'));
Thank you
James
___
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
___
postgis-users mailing list
postgis-users@postgis.refractions.net
Re: [postgis-users] Enter details into Geom column
Hi Ben
Try instead of public.CLEANEDCAMDENGPS -- public.CLEANEDCAMDENGPS
notice the .
I strongly suggest to lowercase all your table and column names.
Hope this helped.
Ricardo
2011/5/30 Ben Madin li...@remoteinformation.com.au
James,
The error message has two possibilities:
ERROR: relation public.CLEANEDCAMDENGPS does not exist
either their is no table called 'CLEANEDCAMDENGPS' (and I think you've
probably got the idea about the issues with case), but it is also possible
that the schema is not 'public', depending on your access to a database. If
it is on your local computer, and you have no idea what I'm talking about,
then it probably is public. If you are using a shared installation, it may
not be public.
if you are using psql (the command line), and try a command like \dt, you
will see a list of the 'relations' which do exist - something like
List of relations
Schema | Name | Type | Owner
+--+---+---
backoffice | access | table | ben
backoffice | accesslevel | table | ben
backoffice | categories | table | ben
if your table is not in this list, (it's specified as backoffice.access for
instance) then it may not exist, or you need to look up search_path.
cheers
Ben
On 31/05/2011, at 2:35 AM, James Smith wrote:
Dear Brent,
Thank you for your reply and simple explanation, it's much appreciated.
Unfortunately, it doesn't seem to work. When I try to create the Geom
column, I get this error:
-
ERROR: function st_creategeometrycolumn(unknown, unknown, unknown, integer,
unknown, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Character: 8
-
So I browsed the PostGIS functions, and thought that I should perhaps use
the function 'AddGeometryColumn' instead, so changed the code to below:
select AddGeometryColumn('public',
'CLEANEDCAMDENGPS','geom',4326,'POINT',2);
However this returns an error of the below:
ERROR: relation public.CLEANEDCAMDENGPS does not exist
I should perhaps say at this point, that the table called CLEANEDCAMDENGPS
is within a database called CAMDENGPS. I played around with trying to put
the database name into the statement too, but with no luck.
Any thoughts?
Thanks again, and yes, I'll take onboard your point about captials and
table names from this point forwards.
Cheers
James
On 29 May 2011 23:44, pcr...@pcreso.com wrote:
Hi James,
I suggest you avoid upper case letters in table column names if you can.
It makes a few things easier
The syntax in both SQL statements is wrong. Try:
select
ST_CreateGeometryColumn('public','CLEANEDCAMDENGPS','geom',4326,'POINT',2);
the fields are (in order):
schema where table can be found ('public')
the table name where you want the new column ('CLEANEDCAMDENGPS')
the name of the geometry column to create ('geom')
the SRID of the geometry column to create (4326)
the geometry type ('POINT')
the number of dimensions (2 - x y)
All string values need to be quoted.
To populate this column try:
update CLEANEDCAMDENGPS
set geom=setsrid(makepoint(LONGITUDE,LATITUDE),4326);
So, create a point geometry from the two numeric columns (makepoint),
force the SRID of this geometry to 4326 (setsrid), write this value to
your new column (update table set column =).
HTH,
Brent Wood
James Smith wrote:
Dear all,
Would appreciate some help. I have created an existing database (with
PostGIS extension) and it has a table called CLEANEDCAMDENGPS which
is populated with approx 600,000 rows. There are 20 or so columns in the
table, two of which are Latitude and Longitude (WGS84). I would now
like to create a Geom column with points in, the values of which
should be taken from the latitude and longitude column. Could someone
provide me with sample code as to how to do this please? I had a go
with the below, but don't really know what I'm doing... neither of the
statements work...
--CREATE THE COLUMN--
SELECT AddGeometryColumn('CAMDENGPS', 'GPS_POINTS', 'geom', 4326,
'POINT', 2)
--POPULATE THE COLUMN--
INSERT INTO CLEANEDCAMDENGPS (GPS_POINTS)
VALUES ( ST_GEOMFROMTEXT('POINT((SELECT LONGITUDE FROM
CLEANEDCAMDENGPS) (SELECT LATITUDE FROM CLEANEDCAMDENGPS))', 4326,
'Point'));
Thank you
James
___
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
[postgis-users] Get point distance and bearing from known point.
I need a function that gets a point a distance and bearing from a known point on the WGS84 datumn. If postgis has such a function I think I will start using it! I checked the reference manual but my limited knowledge of postgis at this point is not doing me well. Thank you! ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
