PostGIS users

Richard Greenwood | 1 Jul 2011 01:29

Re: OT: Export/copy postgres geometry to sql server with bcp

I'd suggest that you try ogr2ogr.

Rich

On Tue, Jun 28, 2011 at 6:48 AM, Charles Galpin <cgalpin <at> lhsw.com> wrote:
> Sorry for the somewhat off topic question, but I figured someone here has probably dealt with this.  I am
using postgis and am very happy with it, but for various reasons I need to export some of our data to sql
server periodically and want to automate it, preferably to fit into our existing framework which uses bcp
to import from flat files.  However looking at the binary output of a straight "copy" to CSV the binary
formats do not look the same. Is there any way to export to a format that bcp can understand?
>
> I can export the wkt, strip quotes, import into a temp table and then use an insert select to fill the
destination table converting the wkt back to a geometry but this is less than ideal.
>
> tia,
> charles
>
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

--

-- 
Richard Greenwood
richard.greenwood <at> gmail.com
www.greenwoodmap.com

headers

Daniel Weitzenfeld | 1 Jul 2011 02:40

Re: Tiger Geocoder 2010 setup issues

Now that I've got it working, a couple more questions:

"The higher the rating the less likely the geocode is right." Are there heuristics for ranges of ratings and how much to trust the result?

Are there performance advantages to coding in batches?

Thanks again.

On Wed, Jun 29, 2011 at 8:40 PM, Daniel Weitzenfeld <dweitzenfeld <at> gmail.com> wrote:

Thank you for all your help - I got it to work!

For posterity, the postgis-related fixes I made:
-unmarked the ALTER DATABASE line in create_geocode.sh
-changed pg_hba.conf to trust, trust, trust
-tweaked a file with a name like "postgis conf" to allow access on port 5432
-added the projections.sql file to my postgis-enabled db

I also had a bunch of silly problems related to my mediocre linux skills, e.g.
-wget wasn't working because I didn't have permissions to create a directory
-I hadn't installed unzip

Basically I was in over my head but I surfaced. The precision from the geocodes I've run so far has been really amazing, good stuff Regina and Leo!

On Wed, Jun 29, 2011 at 12:13 PM, Paragon Corporation <lr <at> pcorp.us> wrote:

Daniel,

Yap. what we had done is move the declaration up above the CD call since we rna into same issue when testing in Linux. As I recall I think when people complained about the issue, the CD call came before the STATEDIR.

Regarding 1.5.3. You should be fine to run with that. For our production work involving tiger geocoder, we are using PostGIS 1.5 and I think most people are actually and just testing on PostGIS 2.0.

However we did make changes that now require PostgreSQL 8.4 or above. So you need to be running at least PostGIS 1.5 and PostgreSQL 8.4+.

Did you verify you have a tiger schema in your database and that it does have tables -- addr, county, edges, faces etc?

From the addr fatal error you described we guessed 1 of 3 things was happening

a) Your script was pointing at the wrong database and not the one you loaded tiger in

b) The tiger schema wasn't in your search path --- If you look at the create_geocode.sh (you'll see a remarked out line to alter db to add tiger to your database search path), this is a common mistake people make not having that line

which causes the error you described. We left it remarked out since some people have custom search paths and we didn't want to overwrite them.

c) Your database just doesn't have the skeleton and lookup tables or is missing one of them

Like I said your next issue is your pg_hba.conf or you have another instance of PostgreSQL running and you are pointing at the wrong instance.

Regina and Leo,

http://www.postgis.us

From: postgis-users-bounces <at> postgis.refractions.net [mailto:postgis-users-bounces <at> postgis.refractions.net] On Behalf Of Daniel Weitzenfeld

Sent: Tuesday, June 28, 2011 6:52 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Tiger Geocoder 2010 setup issues

Thank you Regina and Leo. It sounds like I might have a version issue.

I'm using PostGIS 1.5.3, as per the installation instructions here: http://postgis.refractions.net/docs/ch02.html

When you suggested redownloading, did you mean all of PostGIS, or just the geocoder?

Is the code here the up to date version? here = http://svn.osgeo.org/postgis/trunk/extras/tiger_geocoder/tiger_2010/
Because in tiger_loader.sql at that location, there's the line:
E'STATEDIR="${staging_fold}/${website_root}/${state_fold}"

Is that the fixed version?

In the meantime, I'll work on my ident issues.

On Tue, Jun 28, 2011 at 11:46 PM, Paragon Corporation <lr <at> pcorp.us> wrote:
Daniel,

Haven't had a chance to recheck our script to confirm its not something
amiss.

Which version of PostGIS are you working with?

You can ignore the does not exist, skipping errors -- those are trying to
drop tables which wouldn't exist for new installs.

The addr does not exist often happens if you don't have tiger in your
database search_path. Make sure its in your search_path. Also you can
verify the table exists
By making sure you have a tiger schema and it has tables like addr, edges
etc.

Regarding your ident problem.

PostgreSQL can listen on localhost or local port and depending on which that
has to be registered in pg_hba.conf

You can try taking out the PGHOST line since you may be running only on a
non-IP port and see if that helps or change your pg_hba.conf to allow.

Regarding this issue:
STATEDIR="/gisdata/www2.census.gov/geo/pvs/tiger2010st/36_New_York"

especially because an early command is

cd $STATEDIR

I'm pretty sure we fixed that so I suspect your version is probably out of
data and you may want to just redownload from PostGIS site (either the SVN
tar ball or directly from svn)

Regina and Leo
http://www.postgis.us

-----Original Message-----
From: postgis-users-bounces <at> postgis.refractions.net
[mailto:postgis-users-bounces <at> postgis.refractions.net] On Behalf Of Daniel
Weitzenfeld
Sent: Tuesday, June 28, 2011 6:51 AM
To: postgis-users <at> postgis.refractions.net
Subject: [postgis-users] Tiger Geocoder 2010 setup issues

Hi,
I'm trying to setup the TIGER Geocoder, following the readme here:
http://svn.osgeo.org/postgis/trunk/extras/tiger_geocoder/tiger_2010/README
I'm setting it up on a centos 5.5 server.
My questions are below, organized by the step in the README.

------| Step 3. Edit the tiger_loader.sql to the paths of your
executables server etc.
Is the UNZIPTOOL variable supposed to be changed, or left at its default?
UNZIPTOOL=unzip

------| Step 4. Edit the create_geocode.bat or create_geocode.sh and
------| run

When I ran the file, I got a boatload of NOTICEs like the following,
interspersed throughout the output:

psql:tables/lookup_tables_2010.sql:4: NOTICE: table "direction_lookup" does
not exist, skipping
psql:tables/lookup_tables_2010.sql:39: NOTICE: table
"secondary_unit_lookup" does not exist, skipping

Is this something I should be concerned about? Did I skip something?

Perhaps more alarmingly, the output finished with this:

COMMIT
ERROR: relation "addr" does not exist

Again, what should I do?

------| 8. Run this command at psql or pgAdmin III query window to
generate the script.
The first line of the generated script doesn't look right:

STATEDIR="/gisdata/www2.census.gov/geo/pvs/tiger2010st/36_New_York"

especially because an early command is

cd $STATEDIR

Also, when I try to run the generated script anyway, I get a boatload of
these:

psql: FATAL: Ident authentication failed for user "daniel"

...which is strange to me because I user I thought user daniel had rights on
the PGDATABASE. Indeed, user daniel can create/delete schemas on it. Am I
misunderstanding something about postgres permissions?

Thanks in advance for any guidance you can provide.
Best,
-Daniel

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

headers

Paragon Corporation | 1 Jul 2011 07:48

Re: OT: Export/copy postgres geometry to sql server with bcp

Charles,

We haven't tried ourself, but someone posted on our OGR2OGR tip page his
experience and some gotchas and how to work around them. 

http://www.bostongis.com/PrinterFriendly.aspx?content_name=ogr_cheatsheet

If you are not against shelling out some money.  One of our clients uses
Safe FME for ETL. http://www.safe.com/   It does both PostGIS and SQL
Server.  I'm not much of a GUI liker for loading so I personally haven't
used it myself, but it does seem to have a SQL Server SSIS feel to it
If you are into that kind of stuff.

 Regina and Leo,
http://www.postgis.us

-----Original Message-----
From: postgis-users-bounces <at> postgis.refractions.net
[mailto:postgis-users-bounces <at> postgis.refractions.net] On Behalf Of Richard
Greenwood
Sent: Thursday, June 30, 2011 7:29 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] OT: Export/copy postgres geometry to sql server
with bcp

I'd suggest that you try ogr2ogr.

Rich

On Tue, Jun 28, 2011 at 6:48 AM, Charles Galpin <cgalpin <at> lhsw.com> wrote:
> Sorry for the somewhat off topic question, but I figured someone here has
probably dealt with this.  I am using postgis and am very happy with it, but
for various reasons I need to export some of our data to sql server
periodically and want to automate it, preferably to fit into our existing
framework which uses bcp to import from flat files.  However looking at the
binary output of a straight "copy" to CSV the binary formats do not look the
same. Is there any way to export to a format that bcp can understand?
>
> I can export the wkt, strip quotes, import into a temp table and then use
an insert select to fill the destination table converting the wkt back to a
geometry but this is less than ideal.
>
> tia,
> charles
>
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

--
Richard Greenwood
richard.greenwood <at> gmail.com
www.greenwoodmap.com
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

headers

John Callahan | 1 Jul 2011 17:34

job opportunity for a web project

[Hopefully, it's OK to post job opportunities to this list. It seems as though PostGIS is central to getting this job done, though I am not sure. Mapserver also may have a role.]

I'm looking to hire someone to help on a web project I'm working on. We use Postgres/PostGIS as the database, MapServer as the map engine, and OpenLayers/GeoExt as the javascript mapping framework on the front end. Server-side: PHP/Python, Windows, Apache.

The project involves displaying numerous spatial data layers on a map with common functionality; this part has already been done, (see attached for a screenshot.) However, we need to add several specific tasks, such as:

- display subsets of points based on a certain criteria selected by user via radio-buttons
- based on mouse click, return values of the polygon or raster grid cell clicked within
- use attribute of a clicked polygon to perform queries/reporting from other tables
- if a groundwater well is click on (points), then display time series graph of data
- based on user selected criteria, select points and return as line and display on map

If you would be interested in this opportunity or think this post would be more appropriate on another list, please let me know. It's fine with me if any work done is shared back to the community. Start date is anytime, sooner the better. Thanks.

- John

***********************************
John Callahan, Research Scientist
Delaware Geological Survey
University of Delaware
URL: http://www.dgs.udel.edu
*******************************

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

headers

Brian Sanjeewa Rupasinghe | 1 Jul 2011 17:58

SQL query

Hi,

I am using following SQL query to retrieve the coordinates of the first vertex of the first multipolygon in the

table Building_Poly.

select X(pointN(ExteriorRing(GeometryN(the_geom,1)),1)), Y(pointN(ExteriorRing(GeometryN(the_geom,1)),1)) from

building_poly limit 1;

In order to get the other coordinates of the vertices of the polygon, i want to find out the number of vertices of the

multipolygon and then iterate through. How to do this using a loop?

Cheers, sanjeewa.

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

headers

Maxime van Noppen | 1 Jul 2011 18:03

Re: SQL query

On 07/01/2011 05:58 PM, Brian Sanjeewa Rupasinghe wrote:
> Hi,
>
> I am using following SQL query to retrieve the coordinates of the first
> vertex of the first multipolygon in the
> table Building_Poly.
>
> select X(pointN(ExteriorRing(GeometryN(the_geom,1)),*1*)),
> Y(pointN(ExteriorRing(GeometryN(the_geom,1)),*1*)) from
> building_poly limit 1;
>
> In order to get the other coordinates of the vertices of the polygon, i
> want to find out the number of vertices of the
> multipolygon and then iterate through. How to do this using a loop?

Hi,

You probably want to use ST_DumpPoints :

   http://postgis.refractions.net/docs/ST_DumpPoints.html

--

-- 
Maxime

headers

Jeff McKenna | 2 Jul 2011 17:25

Re: postgis_restore.pl createdb options - enhancement


Quick update: although this change was applied to the PostGIS 1.4 branch, it
has not been applied to the PostGIS 1.5 branch.

-jeff

---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/

Jeff McKenna wrote:
> 
> The 'postgis_restore.pl' contrib file does allow the user to specify  
> createdb options, however the script does not use them unless you make  
> the following changes to the script:
> 
> - line 688: "createlang  plpgsql $dbname;" =====> change to  
> "createlang $createdb_opt plpgsql $dbname`;"
> - line 693: "-a $dbname" =====> change to "psql $createdb_opt -a  
> $dbname"
> 
> Can this be added into the next release of this contrib file?
> 
> thanks.
> 

--

-- 
View this message in context: http://old.nabble.com/postgis_restore.pl-createdb-options---enhancement-tp18762156p31979959.html
Sent from the PostGIS - User mailing list archive at Nabble.com.

headers

Denis Rykov | 3 Jul 2011 17:41

PostGIS 2.0 installation error

ERROR: could not load library "/usr/lib/postgresql/9.0/lib/postgis-2.0.so": /usr/lib/postgresql/9.0/lib/postgis-2.0.so: undefined symbol: DirectFunctionCall3Coll
More detailed information at :
http://gis.stackexchange.com/questions/11759/postgis-installation-error

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

headers

James David Smith | 3 Jul 2011 18:15

PG Routing

Dear all,

I have a table of GPS points which are the locations of vehicles every
15 seconds. Each journey that a car makes has an ID. For example a
taxi is on 'job 1' and has 50 points while it does this job, and then
another 50 or 60 when it is on 'job 2' etc. What I would like to do
however is investigate whether the car took the most effecient route
between the two points. To this end I think that I can use the
PGROUTING extension of PostGIS...?  Does this sound possible? I have
found the PGROUTING website, and download the ZIP file for Windows
installation, however I am not sure how to install it. Could somebody
please tell me how or provide a link to a good place to read about how
to install and use PGROUTING?  Alternatively, is there a better way of
accomplosing my task?

Best wishes

James

headers

Brian Sanjeewa Rupasinghe | 3 Jul 2011 18:29

minimum value of the query result

Hi,

I retreive minimum x of each bounding box of some polygons using following query:

SELECT (min(ST_XMin(BOX2D((ST_Dump(the_geom)).geom)))) as XMin

from building_poly;

How can i find the minimum X of all Xmin coordinatges? Can i use min()?

Cheers, Sanjeewa.

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

107	May 2013
141	April 2013
94	March 2013
201	February 2013
280	January 2013
220	December 2012
302	November 2012
348	October 2012
247	September 2012
149	August 2012
299	July 2012
289	June 2012
468	May 2012
541	April 2012
612	March 2012
561	February 2012
244	January 2012
352	December 2011
302	November 2011
371	October 2011
249	September 2011
240	August 2011
322	July 2011
352	June 2011
222	May 2011
196	April 2011
320	March 2011
232	February 2011
290	January 2011
241	December 2010
180	November 2010
219	October 2010
223	September 2010
251	August 2010
326	July 2010
151	June 2010
323	May 2010
193	April 2010
323	March 2010
176	February 2010
325	January 2010
196	December 2009
280	November 2009
299	October 2009
310	September 2009
266	August 2009
320	July 2009
179	June 2009
282	May 2009
201	April 2009
235	March 2009
243	February 2009
322	January 2009
265	December 2008
278	November 2008
384	October 2008
338	September 2008
329	August 2008
303	July 2008
481	June 2008
499	May 2008
444	April 2008
324	March 2008
240	February 2008
369	January 2008
196	December 2007
297	November 2007
361	October 2007
285	September 2007
342	August 2007
341	July 2007
358	June 2007
346	May 2007
382	April 2007
266	March 2007
306	February 2007
259	January 2007
278	December 2006
341	November 2006
303	October 2006
326	September 2006
237	August 2006
271	July 2006
256	June 2006
323	May 2006
270	April 2006
379	March 2006
351	February 2006
505	January 2006
318	December 2005
331	November 2005
337	October 2005
355	September 2005
296	August 2005
277	July 2005
323	June 2005
378	May 2005
322	April 2005
418	March 2005
305	February 2005
280	January 2005
185	December 2004
247	November 2004
300	October 2004
247	September 2004
246	August 2004
161	July 2004
192	June 2004
224	May 2004
257	April 2004
279	March 2004
297	February 2004
149	January 2004
174	December 2003
166	November 2003
192	October 2003
152	September 2003
243	August 2003
129	July 2003
141	June 2003
130	May 2003
116	April 2003
1	November 2002
1	August 2001

Mon	Tue	Wed	Thu	Fri	Sat	Sun

				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31