Re: [postgis-users] break road linestrings intelligently
Martin, Jan, Sandro - Thanks for the suggestions. I think I'm pointed in the right direction. Comparing the end points to generate the sequential order makes sense, should have thought of that myself. Always good to hear from you guys! Thanks, Rich On Wed, Sep 26, 2012 at 6:00 AM, Sandro Santilli s...@keybit.net wrote: On Tue, Sep 25, 2012 at 07:43:10PM -0600, Richard Greenwood wrote: I break the original linestring into 3 new linestrings, but how do I populate the from and to values? If I know the order of the 3 new linestrings it would be very easy to calculate the new values based on the length of each new linestring, eg. line #1 100-240, line#2 241-385, line #3 386-500 or some such thing. What I've been doing in topology for determining the order has been comparing the location of a point of each substring against the parent string. --strk; http://www.cartodb.com - Map, analyze and build applications with your data ~~ http://strk.keybit.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] break road linestrings intelligently
I have a table of linestrings representing road centerlines. The linestrings have the common from and to address min/max values. But the linestrings are not broken at all of the intersecting streets. So I need to break the linestrings at all intersections, but I need to populate the from and to values appropriately in the new linestrings. The addressing is based on line length. So I might start with a linestring that goes from 100 to 500 and has two intersecting streets. I break the original linestring into 3 new linestrings, but how do I populate the from and to values? If I know the order of the 3 new linestrings it would be very easy to calculate the new values based on the length of each new linestring, eg. line #1 100-240, line#2 241-385, line #3 386-500 or some such thing. It seems like something that topology should help with, but I'm not sure where to start. Sorry if I haven't clearly defined my problem. Any suggestions are appreciated. Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] SRID in geometry_columns view
On Tue, Jul 24, 2012 at 10:40 PM, Mike Toews mwto...@gmail.com wrote: On 25 July 2012 13:48, Richard Greenwood richard.greenw...@gmail.com wrote: Okay, but there is an example in the doc's showing just geometry(srid) which doesn't work for me. Guess that's what you're saying? I now see what you are looking at, and it's a typo in the docs. I'm dealing with a view, not a table. My table shows the correct srid in geometry_columns but the view which is based upon the table shows a srid of 0. I don't want or need to transform the geometry. I just need for its srid to be correctly reflected in the geometry_columns view. OK, I follow you correctly now. You are using the older-style constraints on your table, which looks something like: ALTER TABLE my_table ADD CONSTRAINT enforce_srid_wkb_geometry CHECK (st_srid(wkb_geometry) = 3739); Although this shows the correct SRID for the table in the geometry_columns view, it doesn't propagate further to derived views. The simplest way to get this to work is to drop the older style constraint, and use the new 2.0 typmod syntax, described above. ALTER TABLE my_table DROP CONSTRAINT enforce_srid_wkb_geometry; ALTER TABLE my_table DROP CONSTRAINT enforce_geotype_wkb_geometry; -- you'll also need to temporarily drop your view; now, e.g. set as Point ALTER TABLE my_table ALTER COLUMN wkb_geometry TYPE geometry(Point,3739) USING ST_SetSRID(wkb_geometry,3739); After restoring your view, you should see the correct geometry type and SRID for the source table, and all derived views. Got it. You are correct that I was using the old style constraints and by switching to the typmod syntax I now see the correct srid in views derived from the table. Thanks you very much for your help. Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] SRID in geometry_columns view
On Wed, Jul 25, 2012 at 12:10 AM, Sandro Santilli s...@keybit.net wrote: I now see what you are looking at, and it's a typo in the docs. Could any of you guys file a ticket with a patch for this ? Ticket #1934 -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] SRID in geometry_columns view
I am having difficulty getting my views' SRIDs into the geometry_columns view in PostGIS 2.0. The doc's [1] suggest casting the geometry in the view so I tried: wkb_geometry::geometry(3739) which generates the error: ERROR: Invalid geometry type modifier: 3739 next I tried: wkb_geometry::geometry(Geometry,3739) which generates the error: ERROR: cannot change data type of view column wkb_geometry from geometry to geometry(Geometry,3739) Any suggestions would be appreciated. Rich [1] http://postgis.refractions.net/docs/using_postgis_dbmanagement.html#geometry_columns -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] SRID in geometry_columns view
On Tue, Jul 24, 2012 at 4:10 PM, Mike Toews mwto...@gmail.com wrote: On 25 July 2012 09:49, Richard Greenwood richard.greenw...@gmail.com wrote: I am having difficulty getting my views' SRIDs into the geometry_columns view in PostGIS 2.0. The doc's [1] suggest casting the geometry in the view so I tried: wkb_geometry::geometry(3739) which generates the error: ERROR: Invalid geometry type modifier: 3739 Yup, this invalid, but it is not what the manual says. The typmod is either: geometry(type,srid) or if SRID is not known, then geometry(type), where 'type' can be one of Geometry, Point, PointZ, etc, etc. Okay, but there is an example in the doc's showing just geometry(srid) which doesn't work for me. Guess that's what you're saying? next I tried: wkb_geometry::geometry(Geometry,3739) which generates the error: ERROR: cannot change data type of view column wkb_geometry from geometry to geometry(Geometry,3739) You need to either assign an SRID or reproject to that SRID. Are you geometries mixed? If not, you might want to use a more specific geometry type, like Polygon or MultiPolygon, etc., rather than Geometry. My issue is trying to get the the geometry_columns view to recognize my srid, not geometry type, but I did try specific geometry types which produced the same error. To assign a missing or incorrect SRID[1]: ALTER TABLE my_table ALTER COLUMN wkb_geometry TYPE geometry(Geometry,3739) USING ST_SetSRID(wkb_geometry,3739); Or if it needs to be transformed (reprojected) to a different SRID[2]: ALTER TABLE my_table ALTER COLUMN wkb_geometry TYPE geometry(Geometry,3739) USING ST_Transform(wkb_geometry,3739); I'm dealing with a view, not a table. My table shows the correct srid in geometry_columns but the view which is based upon the table shows a srid of 0. I don't want or need to transform the geometry. I just need for its srid to be correctly reflected in the geometry_columns view. Using ST_SetSRID() as you suggest above in the creation of the view works in so far as that it doesn't generate an error when I create my view, but geometry_columns still lists a srid of 0 for my view. Thanks for your suggestions, but I still don't get it. Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Z coordinate value changes with st_transform
___ 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 -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] tool to load dbf into postgresql table
It's been around for years. Since 1.4 I'd guess. When you type shp2pgsql you should see this option: -n Only import DBF file. The OP was how to load a dbf into postgres, you're exporting with pgsql2shp, which will export only the dbf if there is not geometry column in the table being exported. Rich On Sun, Jan 29, 2012 at 9:39 PM, pcr...@pcreso.com wrote: When did this become available? I'm using pgsql2shp from Postgis version 1.5.3 for Linux, which is pretty current, and that is not a listed command line option. Cheers, Brent Wood --- On *Mon, 1/30/12, Richard Greenwood richard.greenw...@gmail.com*wrote: From: Richard Greenwood richard.greenw...@gmail.com Subject: Re: [postgis-users] tool to load dbf into postgresql table To: PostGIS Users Discussion postgis-users@postgis.refractions.net Date: Monday, January 30, 2012, 3:55 PM On Sun, Jan 29, 2012 at 7:17 PM, John Morgan jdmor...@unca.eduhttp://mc/compose?to=jdmor...@unca.edu wrote: Hello, This may be more of a postgresql question, but it is related to my postgis setup. I was hoping that someone might know of an easy way to load a dbf into postgresql table. I have used the gdal tool shp2pgsql to load a shapefile in this way and it worked great. Is there a similar tool for loading just a dbf? Thanks, Derek Derek, Use the -n switch with shp2pgsql will do what you want: shp2pgsql -n Also ogr2ogr will do the trick. ogr2ogr can also handle conversions between numerous other spatial and non-spatial data formats. ODBC is especially handy. Rich -- Richard Greenwood richard.greenw...@gmail.comhttp://mc/compose?to=richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.nethttp://mc/compose?to=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 -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] tool to load dbf into postgresql table
On Sun, Jan 29, 2012 at 7:17 PM, John Morgan jdmor...@unca.edu wrote: Hello, This may be more of a postgresql question, but it is related to my postgis setup. I was hoping that someone might know of an easy way to load a dbf into postgresql table. I have used the gdal tool shp2pgsql to load a shapefile in this way and it worked great. Is there a similar tool for loading just a dbf? Thanks, Derek Derek, Use the -n switch with shp2pgsql will do what you want: shp2pgsql -n Also ogr2ogr will do the trick. ogr2ogr can also handle conversions between numerous other spatial and non-spatial data formats. ODBC is especially handy. Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] st_collect returning more geometries than I want
This seems so simple that I'm embarrassed to ask, but, putting my pride in my pocket... I want to aggregate two linestrings into one. The endpoint of the first linestring is coincident with the start point of the second. Using st_collect() or st_union() I get a multiLineString with 2 geometries. How can I get a single linestring, or a multiLineString with just 1 geometry? I cases where I aggregate linestrings that do not have coincident start/end points I do want multiLineStrings with multiple geometries. I tried the st_dump() trick with no better luck: SELECT label, st_numgeometries(ST_Collect (f.the_geom )) FROM ( SELECT label, (ST_Dump (wkb_geometry )).geom As the_geom from road_cl ) As f GROUP BY label Basically I'm looking for the opposite of st_multi() Thanks, Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] st_collect returning more geometries than I want
Thanks Steve, that looks like what I need. Best regards, Rich On Tue, Jan 3, 2012 at 7:49 AM, Stephen Woodbridge wood...@swoodbridge.com wrote: On 1/3/2012 9:19 AM, Richard Greenwood wrote: This seems so simple that I'm embarrassed to ask, but, putting my pride in my pocket... I want to aggregate two linestrings into one. The endpoint of the first linestring is coincident with the start point of the second. Using st_collect() or st_union() I get a multiLineString with 2 geometries. How can I get a single linestring, or a multiLineString with just 1 geometry? I cases where I aggregate linestrings that do not have coincident start/end points I do want multiLineStrings with multiple geometries. I tried the st_dump() trick with no better luck: SELECT label, st_numgeometries(ST_Collect (f.the_geom )) FROM ( SELECT label, (ST_Dump (wkb_geometry )).geom As the_geom from road_cl ) As f GROUP BY label Basically I'm looking for the opposite of st_multi() Thanks, Rich Hi Rich, I think you are looking for: http://postgis.refractions.net/docs/ST_LineMerge.html -Steve ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Encoding problem UTF-8
Bob, Before running ogr2ogr try setting: set PGCLIENTENCODING=LATIN1 Rich On Wed, Dec 14, 2011 at 11:28 AM, Bob Pawley rjpaw...@shaw.ca wrote: Hi I am attempting to import a dxf file to Postgis using ogr2ogr version 1.8 running on Win 7. I get errors “invalid byte sequence for encoding UTF8: 0xfe” using the following command in command prompt. Points and text are not being imported. set DXF_INLINE_BLOCKS=falseset DXF_MERGE_BLOCK_GEOMETRIES=falseogr2ogr --config DXF_ENCODING UTF-8 -f PostgreSQL PG:host=localhost user=postgres dbname=IDW password=calgary0623 Water_Ex.dxf -nln Import_PID -overwrite -skipfailures During research of this problem I came upon a suggestion to check with the Postgre list. Can anyone suggest a solution? Following are a few of the error messages. Bob LIST', '{1,1,1}', 0) RETURNING ogc_fid ERROR 1: INSERT command for new feature failed. ERROR: invalid byte sequence for encoding UTF8: 0xfe Command: INSERT INTO import_pid (wkb_geometry , layer, subclasses, exte ndedentity, entityhandle, blockname, blockscale, blockangle) VALUES (Ge omFromEWKT('SRID=-1;POINT (0.003291735334244 -0.065895915915561)'::TEXT) , 'MC-A TT', 'AcDbEntity:AcDbBlockReference', ' 4 tag_rev1 t 1 KEYTAG■C,10,0 RE V_ID■C,10,0 REVISION■C,3,0 REVISED_BY■C,10,0 REVISED_ON■C,14,0 REV_TITLE■C,32,0 REV_BY_APP■C,16,0 APPROVEDBY■C,10,0 PENDING■C,1,0 PEND_MAIL■C,10,0', '327A', '_A T_DIRLIST', '{1,1,1}', 0) RETURNING ogc_fid ERROR 1: INSERT command for new feature failed. ERROR: invalid byte sequence for encoding UTF8: 0xfe Command: INSERT INTO import_pid (wkb_geometry , layer, subclasses, exte ndedentity, entityhandle, blockname, blockscale, blockangle) VALUES (Ge omFromEWKT('SRID=-1;POINT (0.003291735334244 -0.065895915915561)'::TEXT) , 'MC-A TT', 'AcDbEntity:AcDbBlockReference', ' 4 tag_rev2 t 1 REV_ID■C,10,0 RE V_DETAIL■C,50,0 REV_INT■N,3,0', '327B', '_AT_DIRLIST', '{1,1,1}', 0) RETURNING o gc_fid ERROR 1: INSERT command for new feature failed. ERROR: invalid byte sequence for encoding UTF8: 0xfe ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Attribute query at lat./long. coordinate
On Sun, Nov 27, 2011 at 12:09 PM, Peter Weilandt peterweila...@shaw.ca wrote: I came across PostGIS in my search for a tool to use in a website. I want to develop a website that will use a coordinate (lat long), which is easily obtained from a user on the client side by clicking on a Google map, and then transferring this coordinate to the server side where it will be used to query 4 shapefiles, with each shapefile containing many (800+) overlapping polygons, extracting the values from 4 fields of the attribute table for each polygon that contains this coordinate (drill-down results), saving these values in tabular format for immediate processing, and then returning derived values to the user on the client side. There is no map generation required, only extracting data from the attribute table. This sounds simple in concept, but is PostGIS a tool that can accomplish the query portion of this process and, if so, are there any examples showing the code to use for such a query? Peter, I may have been the one who here from your post on the mapserver list. PostGIS most certainly can do what you describe. A simple example: SELECT * FROM polygon_table WHERE St_Intersects( point, polygon_table.geometry); I cheated a little there - you will need to create a point geometry, so expanding that example slightly: SELECT * FROM polygon_table WHERE St_Intersects( ST_Point($x, $y), polygon_table.geometry); The above assumes $x and $y came from your user's point click and are being passed into your SQL query via a server-side programming language such as PHP. You may need to define the spatial reference system (SRID) with: ST_SetSRID( ST_Point($x, $y), 4326 ) HTH, Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
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 cgal...@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@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] reverse geocoding
On Sat, Apr 3, 2010 at 11:05 AM, Randall, Eric erand...@eriecountygov.org wrote: -- Here's my go at it. Paste into favorite sql editor for easier reading. Cheers. -Eric Really nice! Initially my sides were NULL because my road centerlines were LINESTRING rather than MULTILINESTRING which caused ST_GeometryN() to return NULL. Used ST_Multi() to cast them, and am now getting good looking results. Thanks Eric, I owe you a few beers! Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] reverse geocoding
Steve, Kevin, Brian - Thank you for the pointers. They provide a good starting point. Eric Randall also provided a very interesting outline that takes it quite a bit further. In addition to finding the street which is nearest to a given point, I need to determine what the min and max address values are of the points which are adjacent to a street segment, and also determine whether odd or even numbers are to the left or right side of each segment. I'm not sure how far I will ultimately get with this, but I do appreciate all of your help. Best regards, Rich On Tue, Mar 30, 2010 at 12:29 AM, Brian Modra br...@zwartberg.com wrote: On 30/03/2010, Kevin Neufeld kneuf...@refractions.net wrote: On 3/29/2010 9:58 PM, Stephen Woodbridge wrote: If you have a POINT then you can find the closest centerline within some RADIUS with: select *, distance(POINT, the_geom) as dist from lines where expand(POINT, RADIUS) the_geom order by dist limit 1; -Steve Or to find the closest centerline for all points in your point table: SELECT DISTINCT ON (a.gid) a.*, b.*, ST_Distance(a.geom, b.geom) AS dist FROM points a, lines b WHERE ST_Expand(a.geom, RADIUS) b.geom ORDER BY a.gid, dist ASC; I've found some data sets have very long road linestrings, and this makes the spatial index less effective, so if this is the case, you want to create a new table of roads (for search purposes only) and break the linestrings up into smaller linestrings. I used a plpgsql function that did a select on the entire roads line table, and row by row did this: calculate the length of the linestring using length2d do simple maths to work out how many segments I wanted then in a for loop, used line_substring to create the segments. Insert each segment into a new table, copying across the UID of the original linsestring Then of course, create a spatial index on this new (larger) table. To do the reverse geocoding, I then search using a SQL very similar to Kevin's, and also search on a points table (points of interest such as Church, Petrol Station etc) Then also on the polygons tables to get the suburb etc. The hardest part of reverse geocoding is that you need to become very fmiliar with your data set, and set up the parameters of your search so that the results are sensible. Sometimes the data set is not so good, and you need to make a lot of tweaks to work around this. Cheers, Kevin ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] reverse geocoding
I'm not even sure if reverse geocoding is the correct term, but what I am trying to do is improve TIGER road centerline data from known points. I have a set a accurate address points, and I would like to assign fromleft, toleft, fromright, toright ranges to the TIGER centerlines from these points. I am just doing this for one small community, so I don't intend to turn it into a big programming project. i.e. I don't want to spend more time doing it programatically than it would take to do it by hand. But if there is a tool or some code out there that would help, I sure would appreciate any pointers. Thanks, Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] TIGER geocoder with Census 2009 shapefiles
On Fri, Feb 26, 2010 at 9:52 PM, Paragon Corporation l...@pcorp.us wrote: David, As a matter of fact we've been working on that for chapter 10 of our upcoming book and think we have it all working. As a part of the example generation process for our chapter 10, we had to come up with a way to load the tables that works on both windows and Linux. Unfortunately we haven't had a chance to test the Linux loading approach, but is pretty much a parallel of the windows approach. I am following this steps in the ReadMe.txt in http://www.postgis.us/downloads/tiger_geocoder_2009.zip The ReadMe does not mention running tiger_loader.sql or create_geocode.sql which I assume needs to take place. When running tiger_loader.sql I get error: ERROR: relation state_lookup does not exist LINE 189: FROM state_lookup) As s CROSS JOIN loader_platform As platfo... When running create_geocode.sql I get error: ERROR: syntax error at or near \ LINE 21: \i utility/utmzone.sql Thanks, -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] TIGER geocoder with Census 2009 shapefiles
On Mon, Mar 1, 2010 at 7:13 AM, Paragon Corporation l...@pcorp.us wrote: Richard, To answer your question. We kind of glossed over it in Step 4 when we say run the create_geocoder.bat file. Ah! Got it. I didn't have enough coffee or some other lame excuse. Thanks, Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Sphere or Spheroid
On Fri, Oct 30, 2009 at 5:48 PM, Paragon Corporation l...@pcorp.us wrote: The choice of default sphere vs. spheroid is probably something we should ask the general PostGIS users group so cc'ing them since more people frequent that list that would have strong opinions on this subject. So question folks -- is which do you prefer as default behavior for new geography data type faster sphere calculation (with 0.05% - 1% margin error) (I vaguely recall that's what my studies suggested around the globe compared to UTM) or slower more accurate spheroid calculation I think that the default should be to do it right and that doing it fast should be optional. -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to Spatialize DB on PostGIS?
Kristi, It sounds like you are doing everything correctly. If you would like to send me your data, I would be happy to try loading it on my system and see if that provides any clues. Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com On Wed, Oct 7, 2009 at 8:03 PM, Endah Kristi kristy...@yahoo.com wrote: Hi Richard, I’ve never attempted to “spatialize” my db before using Easy Loader (because I don’t know how to do that, hehe). I don’t have constraints on my table. I’m using PostGIS-pg83-1.3.6-1 and PostgreSQL v8.3.7-1. I’ve asked this to PBBI APAC Sydney, and Jesse Hoobler and engineering team told that this problem appeared because I’m not hitting the spatialized database, database has to be spatialized one. They told this is outside of MapInfo domain. So I thought I should do something in PostGIS. I’ll try to ask on the MapInfo-L, I don’t join yet. A long time ago, I’ve found the same problem on MapInfo Spatialware, the polygon polyline can’t be uploaded properly into SQL Server before it’s spatialized. Then I run a script to spatialize that on SQL Server, and I was able to upload them properly. So I thought that PostGIS is the same problem, but if I’m wrong, maybe any advice for me? Kristi ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Live access to PostGIS from MapInfo
On Wed, Oct 7, 2009 at 12:24 AM, InterRob rob.mar...@gmail.com wrote: Thank you for the suggestion; again something I did not think of (!)... So I tried some really 'easy' little datasets; points with some INT attributes. Still no luck, though... It seems that MapInfo v10 is not compatible with PostGIS 1.4.0. This makes me wondering, having the error message in mind (Unable to translate the Spatial Constraint.): is this spatial constraint expressed differently in PostGIS 1.4.0 , comparing it to, say, 1.3.5? One could even think of even very small differences like capitals in functions e.g. st_srid() versus ST_srID() etc. etc. At least success has been reported (by some other users, like you Richard) of working with PostGIS (1.3.5 or lower) from MapInfo... Okay, I just installed Postgres 8.4 and PostGIS 1.4 and uploaded my test table via Easyloader, and then connected to it with un-cached Live access and it seems to be working for me. The spatial constraint that MapInfo is imposing on my table is: st_srid(SP_GEOMETRY) = (-1) What's your spatial constrain? What happens if you drop it? This thread might be valuable on MapInfo-L as I think the issues are more with MI than PostGIS. -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Live access to PostGIS from MapInfo
Just for the record - I have MapInfo Live Access (not cached) working nicely with MI 10.0, pgsql 8.3.6, PostGIS 1.3 on a mixed geometry table (multi-polygons points) of about 14000 rows. Attribute queries are disappointingly slow, but map rendering is very fast. -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to Spatialize DB on PostGIS?
On Tue, Oct 6, 2009 at 6:22 AM, Endah Kristi kristy...@yahoo.com wrote: Dear All, Apologies first, I'm not familiar with PostGIS/PostgreSQL. Could you please explain to me how to spatialize the database on PostGIS? I've installed PostGIS v1.3 and PostgreSQL v8.3.1 for MapInfo Pro v10, but I can't upload .TAB files to PostGIS successfully (for polygon polyline objects -- it become points when uploaded), whereas MIPro v10 supports read/write for PostGIS. I think it needs spatialize db first. Appreciate if anyone here can advise me how to do that on PostGIS, the steps and its script. Thanks in advance! =) Use EasyLoader in the MapInfo Tools Tool Manager menu. Click ODBC button and select or create an ODBC connection to your PostGIS db Click Map Catalog which will create mapinfo.mapinfo_mapcatalog in your PostGIS db Select Source Tables Click Upload -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Live access to PostGIS from MapInfo
On Mon, Oct 5, 2009 at 4:47 PM, Rob Marjot r...@marjot-multisoft.com wrote: Hi Rob(ert)! Good idea! (but no luck) It shows that it fetches some records from the table -- surprisingly this is not a spatial query based on the bounding box of the initial view as it is recorded in the mapinfo_mapcatalog table. Then, after fetching some 50 records (out of 560), the last query is the following: select c.relhasrules, c.relkind, c.relhasoids from pg_catalog.pg_namespace u, pg_catalog.pg_class c where u.oid = c.relnamespace and c.relname = E'dummy_table' and u.nspname = E'public' You might try with a different, maybe simpler, table. This might help you determine if you have a fundamental problem in your setup, or if maybe the 51st row in your table has an object that is causing problems. In MapInfo, I'd select the centroids, and attribute data from your table, and then load it into PostGIS, and then try your live connection. That way you're working with only point objects, but still working in the same coordinate system. Good luck, keep us posted. -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] problems with mapinfo file export to postgis
On Fri, Sep 25, 2009 at 4:19 PM, Karina Guardado kguard...@gmail.com wrote: Hi all, I have exported a Mapinfo file into postgres using the following statement: ogr2ogr -f PostgreSQL -a_srs EPSG:8913 PG:host=localhost user=mysusername dbname=mydatabase password=mypass mytabfile.TAB but when I want to access by mapserver layer I just don't know how to make que query for the geometry information because I found examples using select the_geom from mytable but this work fine with a shapefile but with mapinfo have no idea about the geometry part, so please if someone has an example or solution let me know Unless you use the -nln (New Layer Name) option, ogr2ogr will name the geometry column wkb_geometry. So just use wkb_geometry instead of the_geom. Hope that gets you started. -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] multipoint2point
I do not think you would 'cast' a multi point to a point, I think it would be a disaggregation, but I do not know what command(s) to use. Maybe repost your question with a new subject line such as 'disaggregate multi point'. Good luck, Rich On Tue, Sep 15, 2009 at 2:03 AM, jj@gmx.de wrote: thank you very much ST_MULTI works fine and solves my problem, is there a function to cast a MULTIPOINT to POINT? - Original Message - From: Richard Greenwood richard.greenw...@gmail.com To: PostGIS Users Discussion postgis-users@postgis.refractions.net Sent: Tuesday, September 15, 2009 5:25 AM Subject: Re: [postgis-users] multipoint2point You could use st_multi() to cast them al to multi point: ST_Multi(geometry) Returns the geometry as a MULTI* geometry. If the geometry is already a MULTI*, it is returned unchanged. SELECT ST_MULTI(wkb_geometry) FROM table; On Mon, Sep 14, 2009 at 6:22 AM, jj@gmx.de wrote: Hi, I have a postgistable with different geometrytyps in one table: points and multipoints (get this information via ST_GeometryType). UMN Mapserver have no problems to show the geometrys, but if I try to export this table via ogr2ogr there is an error: Attempt to write non-poin(MULTIPOINT) to point shapefile. How can I cast all my geometrys to point? Jo ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.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 -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] casting a geometry
I am having trouble inserting geometries into a table. The geometry column has a constraint: enforce_geotype_wkb_geometry CHECK (geometrytype(wkb_geometry) = 'MULTIPOLYGON'::text OR wkb_geometry IS NULL); I am attempting to insert what I assume are polygons generated by st_buffer() but they violate the constraint. If I pgsql2shp the polygons out I can insert them into the table, but that's pretty ugly. Thanks, -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] casting a geometry
That what I needed. Thank you very much! Rich On Wed, Jun 3, 2009 at 1:24 PM, Luigi Castro Cardeles luigi.carde...@gmail.com wrote: Hi if they are polygon's, you can turn then into multi ST_Multi(geometry) Returns the geometry as a MULTI* geometry. If the geometry is already a MULTI*, it is returned unchanged. SELECT ST_MULTI(wkb_geometry) FROM table; See if your postgis version have that function. best regards, Luigi Castro Cardeles 2009/6/3 Richard Greenwood richard.greenw...@gmail.com I am having trouble inserting geometries into a table. The geometry column has a constraint: enforce_geotype_wkb_geometry CHECK (geometrytype(wkb_geometry) = 'MULTIPOLYGON'::text OR wkb_geometry IS NULL); I am attempting to insert what I assume are polygons generated by st_buffer() but they violate the constraint. If I pgsql2shp the polygons out I can insert them into the table, but that's pretty ugly. Thanks, -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.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 -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Windows installer conflict
I'm trying to use the PostGIS 1.3.5 Windows installer that is launched by the Application Stack Builder. It fails with Error opening file for writing: PostgreSQL\8.3\bin\libiconv-2.dll. If I stop postgres the installer is able to write the DLL, but then fails because postgres isn't running. Seems like a Catch-22, but maybe I'm missing something? Regards, -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Windows installer conflict
On Wed, May 6, 2009 at 2:22 PM, Steffen Macke sdtef...@gmail.com wrote: Hi Richard, by the Application Stack Builder. It fails with Error opening file for writing: PostgreSQL\8.3\bin\libiconv-2.dll. If I stop postgres the installer is able to write the DLL, but then fails because postgres isn't running. Seems like a Catch-22, but maybe I'm missing something? Simply ignore this error. As libiconv-2.dll is already installed with PostgreSQL now, there is no need for the PostGIS installer to install it again. The installer should offer you an Ignore button for this. Regards, Steffen Oh well now I should have thought of that myself! Thanks, I feel stupid. Regards, -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] The Old Who is using PostGIS survey again?
1) How you use PostGIS? Primarily as a backend for MapServer. My applications serve public, county government data for several Northern Rockies counties. 2) What you find useful about it over anything else? Obviously a spatially enabled database is handy in that spatial and attribute data are stored, and accessed, together. Postgres/PostGIS is cost effective for small governmental bodies. And while cost effective, it is not lacking in features or power. 3) Why you think there should be any book written focused on its use and of course if such a thing were to exist, would you buy it? Books are wonderful things, duh. Before Google that was all we had. Yeah, I'll buy it. -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NAD conversion problem
On Thu, Dec 18, 2008 at 11:20 AM, Bruce Rindahl rind...@lrcwe.com wrote: So if you have a Debian install where do you get the grid shift files and where do you copy them? Thanks Bruce Being a Ubuntu/Debian knob, I am probably not the best person to answer this, but one way is to build Proj from source. Building Proj is very easy as it does not have dependencies. However, if you just do a: ./configure make make install the binary datum grid shift files will be placed in /usr/local/share/proj/ The Ubuntu/Debian convention is for the files to go in /usr/share/proj So to follow the Ubuntu/Debian structure, you would install Proj to a temporary directory, and then copy the binary datum grid shift to /usr/share/proj So step by step: From http://trac.osgeo.org/proj/ download and unpack the source, currently proj-4.6.1.tar.gz or proj-4.6.1.zip and then download the ASCII datum grid shift files proj-datumgrid-1.4.zip. The grid shift files must be unpacked into the Proj source nad/ directory. For example proj-4.6.1/nad Then configure to with a prefix to a temporary location, for example:we ./configure --prefix=$HOME/proj make make install The binary datum grid shift files are created in the 'make install' step. These files are platform specific, so do not copy Windows files to a Linux computer, or vice versa. Now copy the datum grid shift files to /usr/share/proj sudo cp ~/proj/nad/* /usr/share/proj -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NAD conversion problem
On Wed, Dec 17, 2008 at 10:54 AM, Frank Warmerdam warmer...@pobox.com wrote: However, I am troubled that the Ubuntu proj distribution does not seem to include the grid ship files. I have learned that the DebianGIS packagers are unwilling to distribute the grid shift component until there is a licensing statement available for the grid shift file .zip file. I am following up with them. In time I hope Debian and Ubuntu will include grid shift files (possibly as an optional package). Wow, that's a pretty major omission. I of course respect their position, but it impacts anyone depending on Proj as a shared library - PostGIS, MapServer, etc. And they're a bit inconsistent if Debian GRASS is including the grid shifts (but we'll pretend that I didn't say that so that the GRASS maintainers don't get yelled at). Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NAD conversion problem
On Tue, Dec 16, 2008 at 9:57 PM, Richard Greenwood richard.greenw...@gmail.com wrote: On Mon, Dec 8, 2008 at 6:08 PM, Dylan Beaudette debeaude...@ucdavis.edu wrote: Richard Greenwood wrote: The files in http://svn.osgeo.org/metacrs/proj/trunk/proj/nad/ are ASCII. In the nad directory, you need to do: ./configure make install to create the binary grid shift files that proj actually uses. (Proj doesn't read the ASCII files). And I'm not sure that the config-make-install of the proj program does the nad directory. Rich REALLY? I have never heard that the config-make-make install steps would not process the NAD grid files... Can someone verify this? Cheers, Dylan Dylan On my Ubuntu 8.04 with a binary proj installed from the Ubuntu repository I get: echo '-121.00 37.00' | cs2cs +proj=latlong +datum=NAD27 \ +to +proj=latlong +datum=NAD83 121dW 37dN 0.000 # obviously no datum transform So I fetched the proj source and proj-datumgrid-1.4.zip and did ./configure make make install. But still no datum transform from my new copy of proj. So I did: $ nad2bin src/proj-4.6.1/nad/proj-datumgrid-1.4/conus.lla conus and copied it to /usr/share/proj/ and finally I get the expected results: $ echo '-121.00 37.00' | cs2cs +proj=latlong +datum=NAD27 +to +proj=latlong +datum=NAD83 121d0'3.704W 36d59'59.804N 0.000 The only copy of conus that $ locate found on my system prior to my making it with nad2bin was in /usr/lib/grass/etc/nad/ Probably doing $ export PROJ_LIB=/usr/lib/grass/etc/nad/ would have saved me making conus. Following up on this with a little morning clarity... If you un-zip the *.lla fiiles in proj-datumgrid-1.4.zip into the proj/nad directory before you build, then configure, make, install does convert them to binary and copy them to the share/proj directory. However, I am troubled that the Ubuntu proj distribution does not seem to include the grid ship files. -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NAD conversion problem
On Mon, Dec 8, 2008 at 6:08 PM, Dylan Beaudette debeaude...@ucdavis.edu wrote: Richard Greenwood wrote: The files in http://svn.osgeo.org/metacrs/proj/trunk/proj/nad/ are ASCII. In the nad directory, you need to do: ./configure make install to create the binary grid shift files that proj actually uses. (Proj doesn't read the ASCII files). And I'm not sure that the config-make-install of the proj program does the nad directory. Rich REALLY? I have never heard that the config-make-make install steps would not process the NAD grid files... Can someone verify this? Cheers, Dylan Dylan On my Ubuntu 8.04 with a binary proj installed from the Ubuntu repository I get: echo '-121.00 37.00' | cs2cs +proj=latlong +datum=NAD27 \ +to +proj=latlong +datum=NAD83 121dW 37dN 0.000 # obviously no datum transform So I fetched the proj source and proj-datumgrid-1.4.zip and did ./configure make make install. But still no datum transform from my new copy of proj. So I did: $ nad2bin src/proj-4.6.1/nad/proj-datumgrid-1.4/conus.lla conus and copied it to /usr/share/proj/ and finally I get the expected results: $ echo '-121.00 37.00' | cs2cs +proj=latlong +datum=NAD27 +to +proj=latlong +datum=NAD83 121d0'3.704W 36d59'59.804N 0.000 The only copy of conus that $ locate found on my system prior to my making it with nad2bin was in /usr/lib/grass/etc/nad/ Probably doing $ export PROJ_LIB=/usr/lib/grass/etc/nad/ would have saved me making conus. Rich -- Richard Greenwood richard.greenw...@gmail.com www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NAD conversion problem
On Mon, Dec 8, 2008 at 12:19 PM, Peter N. Schweitzer [EMAIL PROTECTED] wrote: Richard Greenwood wrote: On Thu, Dec 4, 2008 at 1:19 PM, Peter N. Schweitzer [EMAIL PROTECTED] wrote: Hello, I have two PostgreSQL installations. One uses 8.3.3 with PostGIS 1.3.3, GEOS 3.0.1, and PROJ 4.6.1 (with proj-datumgrid-1.4). With it I am able to run a command like update county set nad27_geom=ST_Transform(the_geom,4267); My newer installation uses 8.3.5 with PostGIS 1.3.4, GEOS 3.0.3, and PROJ 4.6.1. With this system I get the error message WARNING: transform: -38 (failed to load NAD27-83 correction file) ERROR: transform: couldn't project point: -38 (failed to load NAD27-83 correction file) I know that proj-datumgrid-1.4.zip must be unzipped in the nad/ directory of proj-4.6.1 PRIOR to running configure. I have done this. I have also recompiled and reinstalled proj, GEOS, and PostGIS in that order, running ldconfig at the end of each step. The directory /usr/local/share/proj on each of these systems is identical. I've tried recreating the database cluster, starting and stopping the database instance, rebooting the machine, dropping and rebuilding the spatial database, all to no avail. So I'm looking for some setting that I may have missed, some environment variable, or some trick to tell libproj.so.0.5.5 where its transform files should go. Both systems run Slackware 12.1. I'm at wit's end. Any clues? Pete PROJ_LIB is an environment variable that points to the directory containing grid shift files and EPSG parameter files. The grid shift files are binary and platform specific. Is it possible that you are using Windows grid shift files on a Linux system? You could also rule out issues related to the different versions of Postgres and PostGIS on you two systems by testing at the command line with the cs2cs utility which is a part of Proj. Rich, Thanks for your counsel. The example using cs2cs from its documentation worked the same on both systems, executed from the command line. I believe the grid shift files are slightly different, even though I thought I had installed the same versions. As a test, I copied the /usr/local/share/proj directory from the older system (the one that does the datum shift without complaint) to the newer one, and the ST_Transform completes without error. So I believe that is the source of the problem. But there is only one link on http://trac.osgeo.org/proj/ for the datum shift file package. If the package is OS-specific I would expect to see more than one. So I am puzzled and worried that next time I may face the same problem again. Peter The files in http://svn.osgeo.org/metacrs/proj/trunk/proj/nad/ are ASCII. In the nad directory, you need to do: ./configure make install to create the binary grid shift files that proj actually uses. (Proj doesn't read the ASCII files). And I'm not sure that the config-make-install of the proj program does the nad directory. Rich -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] NAD conversion problem
On Thu, Dec 4, 2008 at 1:19 PM, Peter N. Schweitzer [EMAIL PROTECTED] wrote: Hello, I have two PostgreSQL installations. One uses 8.3.3 with PostGIS 1.3.3, GEOS 3.0.1, and PROJ 4.6.1 (with proj-datumgrid-1.4). With it I am able to run a command like update county set nad27_geom=ST_Transform(the_geom,4267); My newer installation uses 8.3.5 with PostGIS 1.3.4, GEOS 3.0.3, and PROJ 4.6.1. With this system I get the error message WARNING: transform: -38 (failed to load NAD27-83 correction file) ERROR: transform: couldn't project point: -38 (failed to load NAD27-83 correction file) I know that proj-datumgrid-1.4.zip must be unzipped in the nad/ directory of proj-4.6.1 PRIOR to running configure. I have done this. I have also recompiled and reinstalled proj, GEOS, and PostGIS in that order, running ldconfig at the end of each step. The directory /usr/local/share/proj on each of these systems is identical. I've tried recreating the database cluster, starting and stopping the database instance, rebooting the machine, dropping and rebuilding the spatial database, all to no avail. So I'm looking for some setting that I may have missed, some environment variable, or some trick to tell libproj.so.0.5.5 where its transform files should go. Both systems run Slackware 12.1. I'm at wit's end. Any clues? Pete PROJ_LIB is an environment variable that points to the directory containing grid shift files and EPSG parameter files. The grid shift files are binary and platform specific. Is it possible that you are using Windows grid shift files on a Linux system? You could also rule out issues related to the different versions of Postgres and PostGIS on you two systems by testing at the command line with the cs2cs utility which is a part of Proj. Good luck, Rich -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Compare SQL Server 2008 Spatial, PostGIS, MySQL functionality
On Thu, Jul 17, 2008 at 5:55 PM, Paragon [EMAIL PROTECTED] wrote: As part of our due diligence work, we are formulating a comparison of the spatial functionality offered by SQL Server 2008 Spatial, PostGIS and MySQL. Its currently in draft, but we hope to release within the next week. We would appreciate if people could take a quick look at it and let us know if you see any gross errors or important things we left out. Most of this information we curled from reading the help docs of each product and from our general familiarity with the databases and GIS products out there. We tried to be as fair as possible. http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008_pos tgis_mysql_compare Thanks, Regina and Leo Nice work. I'm confused in the Spatial Functionality matrix, SQL Server 08 Geography Transformations, you have: Not relevant, does hold of originating source and SRID must be defined in sys.spatial_reference_systems Why isn't that relevant? You should be able to transform between datums, no? -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis Hosting Recommendations Update?
On Tue, Jul 1, 2008 at 10:08 AM, Donald Ijams [EMAIL PROTECTED] wrote: I'm searching for a Postgis enabled web host and noticed the 2006 discussion of this topic on this list. Is there a more recent discussion that I've missed? Is anyone on this list happy with their Postgis enabled web host? Thanks. I am happily using Micro Resources http://mrcc.com/ but I am not sure about Postgis support. -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] SQLite and postGIS
On Fri, Apr 11, 2008 at 9:29 PM, Frank Warmerdam [EMAIL PROTECTED] wrote: Puneet, Rich, SQLite is already supported as a spatial database by OGR. The caveat is that in GDAL 1.5 it is just using a text column with WKT geometries so the spatial performance is not great. To use this with MapServer you would use CONNECTIONTYPE OGR and the CONNECTION string would be the path for the sqlite database. The DATA statement should hold the table name be accessed. Totally cool! I used SpatiaLite (http://www.gaia-gis.it/spatialite/) LoadShapefile() function to import a shapefile into a SQLite db. The geometries are stored as BLOBs in a field named geom Then: sqlite alter table ownership add column WKT_GEOMETRY; sqlite update ownership set WKT_GEOMETRY=astext(geom); Getting MapServer to use the SQLite table was very easy. Recent versions of MS4W have SQLite support in GDAL. So simply adding CONNECTIONTYPE OGR CONNECTION path/to/SQLite.db gets MapServer drawing geometries from SQLite. I'm playing with a table containing about 15,000 polygons and performance is fine. Thanks Frank, for pointing me in the right direction. Rich -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] SQLite and postGIS
On Sun, Apr 13, 2008 at 8:29 PM, Frank Warmerdam [EMAIL PROTECTED] wrote: Martin Chapman wrote: Rich, One thing you should note about OGR performance when accessing certain databases like MySQL, PostgreSQL and SQLite is that OGR uses a strategy for these drivers that gets progressively worse for large datasets. Query execution is always pretty quick in any database, it's accessing the rows quickly that is the true test of database performance in my opinion. In OGR, the SQLite driver becomes progressively slower over time when iterating over a large resultset from 0 to N. Note that doesn't mean that SQLite is slow though, it's actually a really fast database if used optimally. For instance: For each record that is requested after a query is executed, OGR starts at the beginning of the resultset (row zero) and searches forward until it finds the matching FID. This pattern is repeated for every request. This will execute very quickly for the first few thousand FID's or so but the further you iterate through the resultset, and the higher the index of each FID becomes, the slower each record request will become. This is obviously a strategy of diminishing returns over any resultset of any size. The solution to this problem is to use random access. See my other postings to see how to make this change to OGR for MySQL, PostgreSQL and SQLite. Folks, I would like to stress this behavior is only the case when attempting to fetch features by fid. The more common access pattern is to set query parameters (spatial and attribute), and then to iterate through those features matching using GetNextFeature(). In this situation there is none of this search by fid O(n*n) performance. Martin is implementing valuable optimizations for the GetFeature() by FID case but the other alternative that performs well for all OGR datasources is to use the GetNextFeature() iterator. So in a MapServer itemquery, is GetFeature() or GetNextFeature() being used? I'm guessing the latter. I'm trying to picture, from a MapServer perspective, where the senarios you describe would be invoked. Like when would you do a GetFeature() by FID? I guess I should look at the code. My questions are purely academic, so don't waste an undue amount of time trying to enlighten me. Rich -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] SQLite and postGIS
Are any of you driving a MapServer layer with Sqlite, and if so, would you provide an example layer definition? I could sure see a lot of utility in a spatial database that was somewhere between shapefiles and PostGIS. Thanks, Rich -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] shp2pgsql problem with Leopard
On Feb 9, 2008 5:50 PM, Paul Ramsey [EMAIL PROTECTED] wrote: I re-named and found things began working... I am hesitant to commit my patch though, since I know SFA about C at this point. In particular, what bits to rename in getopt.h were not clear to me, so I renamed every *getopt function, which may have been overkill. Try to follow this thread, I was tripped up by SFA, Google suggestions: Solid Front Axle Silica Fume Association Slime Forest Adventure sales force automation Superficial Femoral Artery subcutaneous fat area SMALL FORMAT ART So WTF? -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_function vs function
I'm embarrassed to ask, but what does ST stand for? Rich On 9/10/07, Obe, Regina [EMAIL PROTECTED] wrote: Steve, Just to add to what Mark said. There is a little bit more going on than simply a renaming. Most if not all of the ST relation functions have automatic index use enabled in them. For example the old within is really closer to the hidden _ST_Within and the new ST_Within function is equivalent to writing the old geom1 geom2 AND Within(geom1, geom2) Same goes for ST_Contains, ST_Overlap etc. Hope that helps, Regina From: [EMAIL PROTECTED] on behalf of Mark Cave-Ayland Sent: Sun 9/9/2007 4:30 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] ST_function vs function On Sun, 2007-09-09 at 16:03 -0400, Stephen Woodbridge wrote: Hi all, I seem to have missed reading some vital post or something. What is the difference between ST_function() and function()? Is this just a renaming for namespace issues? Are the function() names deprecated? Are there other benefits to this renaming? Please explain or point me to the relevant docs. Thanks, -Steve Hi Stephen, I don't remember there being much discussion about this, but the relevant commit can be found here: http://postgis.refractions.net/pipermail/postgis-commits/2007-June/74.html. In short, the old function names are deprecated, and new applications should start to use the ST_ prefix functions instead. ATB, Mark. -- ILande - Open Source Consultancy http://www.ilande.co.uk ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users