Re: [postgis-users] Postgis 2.0
On Wed, Nov 30, 2011 at 03:06:46PM -0800, Bob Pawley wrote: Do you yet have a timeline for the Postgis 2.0 release?? Spring at worst, but it's much more likely that you could have something to test by the first days of the new year. A lot depends on how good the postgis users have been according to Santa :) --strk; () Sign the pledge for PostGIS-2.0 Topology ! /\ http://www.pledgebank.com/postgistopology ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] pgsql2shp
Hi, I have some issues with pgsql2shp. I am using postgis 1.5.3 over postgresql 9.0 on windows. The database I am working with is utf8. I have several spatial tables and I want some of them to be exported to a shape file. pgsql2shp seems to be working fine but when I load the generated shape files, then they appear to be empty. No error thrown (on OpenJump). I have been testing it for a while and I came to realize that pgsql2shp does not like tables that only have one field (the spatial one). I can not export a table (any) with only one column but if I add a column to that very table (type character varying) then the exportation works fine and I can load the shp file perfectly At first I thought it was something about the encoding but now I am lost. Any ideas how to solve this? thanks ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] bmd.hasnodata does not exist
Andreas , You don't need to install postgis.sql, rtpostgis.sql Just _upgrade_20_minor.sql ones. That is unless we change the on disk format or change non-droppable things like casts which we might before release. _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen Sent: Wednesday, November 30, 2011 12:30 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] bmd.hasnodata does not exist Thanks! After updating to latest revision i only ran: psql -d database -f postgis.sql psql -d database -f rtpostgis.sql I was not aware that i had to do the _upgrade_20_minor.sql as well. Now it works. Andreas 2011/11/30 Bborie Park bkp...@ucdavis.edu Andreas, Though you upgraded the underlying library, did you run rtpostgis_upgrade_20_minor.sql? Something is definitely out of sync. -bborie On Tue, Nov 29, 2011 at 12:58 AM, Andreas Forø Tollefsen andrea...@gmail.com wrote: No one else had the same issue with 8242? This is exactly the same query as i ran in r.8001, but now it give me the error below in return. Any suggestions? Best, Andreas 2011/11/25 Andreas Forø Tollefsen andrea...@gmail.com Hi, Sorry for posting a lot these days but I am very busy finalizing a project. Previously, I have been running the following query without problems (in rev.8001). After updating to rev.8242 i am getting a strange error. Could someone please see what is wrong? Is it one of the functions that has changed? Query: SELECT gid, AVG(((foo.geomval).val)) FROM (SELECT p.gid, ST_Intersection(p.cell, r.rast) AS geomval FROM mountain r, priogrid_land p WHERE ST_Intersects(p.cell, r.rast) AND p.gid =219260) AS foo GROUP BY gid ORDER BY gid; Error: ERROR: column bmd.hasnodata does not exist LINE 1: SELECT bmd.hasnodataFROM ST_BandMetaData(ras... ^ QUERY: SELECT bmd.hasnodataFROM ST_BandMetaData(rast, nband) AS bmd CONTEXT: PL/pgSQL function _st_intersects line 32 at SQL statement PL/pgSQL function st_intersection line 5 at assignment ** Error ** ERROR: column bmd.hasnodata does not exist SQL state: 42703 Context: PL/pgSQL function _st_intersects line 32 at SQL statement PL/pgSQL function st_intersection line 5 at assignment Thanks for any help. Best regards, Andreas ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] pgsql2shp
On 01/12/11 08:32, toni hernández wrote: Hi, I have some issues with pgsql2shp. I am using postgis 1.5.3 over postgresql 9.0 on windows. The database I am working with is utf8. I have several spatial tables and I want some of them to be exported to a shape file. pgsql2shp seems to be working fine but when I load the generated shape files, then they appear to be empty. No error thrown (on OpenJump). I have been testing it for a while and I came to realize that pgsql2shp does not like tables that only have one field (the spatial one). I can not export a table (any) with only one column but if I add a column to that very table (type character varying) then the exportation works fine and I can load the shp file perfectly At first I thought it was something about the encoding but now I am lost. Any ideas how to solve this? thanks Hi Toni, I don't suppose you've been able to try pgsql2shp from one of the PostGIS 2.0 pre-releases at all? It's had a fairly hefty rewrite and so you might find that this will have solved the issue. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster pixel value
Pierre, This is great you're working on this. It is exactly what I think that pgraster is going to make a great tool. Last days I didn't really have time to check out things, hopefully this week. Cheers, Tom On 30-11-2011 19:19, Pierre Racine wrote: Andreas, Tom, I have put two new functions you might be interested by in http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql The first one, st_areaweightedsummarystats.sql, is usefull when you want to compute summary stats (like the weighted mean) of values coming from a raster table for a series of polygons and that you use ST_Intersection(raster, geometry) to compute the intersection. You normally use it this way: SELECT gt.id, (aws).count, (aws).distinctcount, (aws).geom, (aws).totalarea, (aws).meanarea, (aws).totalperimeter, (aws).meanperimeter, (aws).weightedsum, (aws).weightedmean, (aws).maxareavalue, (aws).minareavalue, (aws).maxcombinedareavalue, (aws).mincombinedareavalue, (aws).sum, (aws).mean, (aws).max, (aws).min FROM (SELECT ST_AreaWeightedSummaryStats(gv) aws FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv FROM rasttable rt, geomtable gt WHERE ST_Intersects(rt.rast, gt.geom) ) foo GROUP BY gt.id ) foo2 The second one, st_summarystatsagg.sql, serve the same purpose but when you are using ST_MapAlgebra() (soon ST_Clip()) to compute the intersection. you normally use it like this: SELECT (ss).count, (ss).sum, (ss).mean, (ss).min, (ss).max FROM (SELECT ST_SummaryStatsAgg(gv) ss FROM (SELECT ST_Clip(rt.rast, gt.geom) gv FROM rasttable rt, geomtable gt WHERE ST_Intersects(rt.rast, gt.geom) ) foo GROUP BY gt.id ) foo2 This is very equivalent to what you were doing. The ST_Clip() replace the ST_MapAlgebra() part and the ST_SummaryStatsAgg() replace the ST_SummaryStats(ST_Union()) part. This should be faster since it does a ST_Union() less. I'm working on a plpgsql version of ST_Clip(). I will post it very soon. Pierre ___ 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] ERROR: missing FROM-clause entry for table
Hi guys, please could someone help me look at this SQL code, I cant figure out what is wrong with it and it gives me this error, ERROR: missing FROM-clause entry for table i LINE 3: Select I.temp_lst_id,I.temp_value as I_t, R.tv.val as R_t, (... ^ Here is the code; Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t From ( Select ST_intersection(R.rast,I.the_geom) AS tv From in_situ_lst I, lst_day R Where I.the_geom R.rast AND ST_intersects(R.rast,I.the_geom) And I.temp_lst_id = 2 )foo; I was just trying to get the difference between a temperature value in a vector temperature table, in_situ_lst and a raster temperature table, lst_day. Regards -- Iyke Maduako Masters in Geospatial Technologies Institute for Geoinformatics,IfGI University of Muenster Germany Phone: +4915129048460 Alternative Email:iykefirstcl...@yahoo.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: missing FROM-clause entry for table
Try this: SELECT I_t, (tv).val as R_t, (I_t - R_t) as D_t FROM (SELECT ST_intersection(R.rast, I.the_geom) AS tv, I.temp_value as I_t FROM in_situ_lst I, lst_day R WHERE I.the_geom R.rast AND ST_intersects(R.rast,I.the_geom) And I.temp_lst_id = 2 ) foo; You don't have to put I.the_geom R.rast in the WHERE... However if your vector table is a table of point you should use ST_Value instead of ST_Intersection. If your table is of polygon ST_Intersection might return many values for one polygon. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of maduako ikechukwu Sent: Thursday, December 01, 2011 8:30 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] ERROR: missing FROM-clause entry for table Hi guys, please could someone help me look at this SQL code, I cant figure out what is wrong with it and it gives me this error, ERROR: missing FROM-clause entry for table i LINE 3: Select I.temp_lst_id,I.temp_value as I_t, R.tv.val as R_t, (... ^ Here is the code; Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t From ( Select ST_intersection(R.rast,I.the_geom) AS tv From in_situ_lst I, lst_day R Where I.the_geom R.rast AND ST_intersects(R.rast,I.the_geom) And I.temp_lst_id = 2 )foo; I was just trying to get the difference between a temperature value in a vector temperature table, in_situ_lst and a raster temperature table, lst_day. Regards -- Iyke Maduako Masters in Geospatial Technologies Institute for Geoinformatics,IfGI University of Muenster Germany Phone: +4915129048460 Alternative Email:iykefirstcl...@yahoo.com mailto:email%3aiykefirstcl...@yahoo.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Batch geocoding (~2 mil addresses)
Thanks all for the suggestions. I am migrating this process to a bigger machine which has 16GB RAM and faster hard drives. I will incorporate these changes once the migration completes. There are two things I got so far I need to do. 1) use pgScript to commit every 500 or 1000 updates 2) send explain analyze to http://explain.depesz.com/ further evaluate the query plan I will keep you posted guys.. Thanks Ravi Ada -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andy Colson Sent: Wednesday, November 30, 2011 8:54 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] Batch geocoding (~2 mil addresses) On 11/29/2011 11:18 PM, Ravi ada wrote: Hello All, I have been fighting with PostGIS for the last week or two to geocode about 2 million addresses. Here are the challenges that I am facing. 1)I sorted the addresses by zip, address, city to group the similar addresses together. 2)So far I have seen only 50% of rating zero, that means perfect match. 3)Some addresses have Suite# or Apt# in address line 1. Geocode function takes longer time for such addresses 4)Addresses that are on the interstate, may be on the service road. These are taking longer to code. How about posting an EXPLAIN ANALYZE to http://explain.depesz.com/. Maybe we can speed it up. -Andy ___ 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] shp2pgsql error - current transaction is aborted...
Hello, I am attempting utilizing the shp2pgsql utility to import a shapefile.The utility reports that the shapefile is of the type MULTIPOLYGON[2] and then proceeds to through a bunch of errors for each record that it can't import current transaction is aborted, commands ignored until end of transaction block.Any advice on how to get around this would be greatly appreciated. Sincerely, -- JDM @ NEMAC ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster pixel value
I have put a first version of ST_Clip in script/plpgsql Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Tom van Tilburg Sent: Thursday, December 01, 2011 7:14 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Raster pixel value Pierre, This is great you're working on this. It is exactly what I think that pgraster is going to make a great tool. Last days I didn't really have time to check out things, hopefully this week. Cheers, Tom On 30-11-2011 19:19, Pierre Racine wrote: Andreas, Tom, I have put two new functions you might be interested by in http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql The first one, st_areaweightedsummarystats.sql, is usefull when you want to compute summary stats (like the weighted mean) of values coming from a raster table for a series of polygons and that you use ST_Intersection(raster, geometry) to compute the intersection. You normally use it this way: SELECT gt.id, (aws).count, (aws).distinctcount, (aws).geom, (aws).totalarea, (aws).meanarea, (aws).totalperimeter, (aws).meanperimeter, (aws).weightedsum, (aws).weightedmean, (aws).maxareavalue, (aws).minareavalue, (aws).maxcombinedareavalue, (aws).mincombinedareavalue, (aws).sum, (aws).mean, (aws).max, (aws).min FROM (SELECT ST_AreaWeightedSummaryStats(gv) aws FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv FROM rasttable rt, geomtable gt WHERE ST_Intersects(rt.rast, gt.geom) ) foo GROUP BY gt.id ) foo2 The second one, st_summarystatsagg.sql, serve the same purpose but when you are using ST_MapAlgebra() (soon ST_Clip()) to compute the intersection. you normally use it like this: SELECT (ss).count, (ss).sum, (ss).mean, (ss).min, (ss).max FROM (SELECT ST_SummaryStatsAgg(gv) ss FROM (SELECT ST_Clip(rt.rast, gt.geom) gv FROM rasttable rt, geomtable gt WHERE ST_Intersects(rt.rast, gt.geom) ) foo GROUP BY gt.id ) foo2 This is very equivalent to what you were doing. The ST_Clip() replace the ST_MapAlgebra() part and the ST_SummaryStatsAgg() replace the ST_SummaryStats(ST_Union()) part. This should be faster since it does a ST_Union() less. I'm working on a plpgsql version of ST_Clip(). I will post it very soon. Pierre ___ 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] Geocoding cross streets?
My question is already answered: someone committed changes at http://trac.osgeo.org/postgis/ticket/1333#comment:3. Are these already in the SVN trunk? Aren On Wed, Nov 30, 2011 at 6:29 PM, Aren Cambre a...@arencambre.com wrote: Based on the conversation below, it appears there is already a solution, and people well-versed with PostGIS may be helping with this already. Given that, are my n00b skillz of much use? Second question--when might this cross street feature be available? I am asking for selfish reasons--I am working on a praxis and have thousands of cross streets I need to geocode. Aren On Tue, Nov 29, 2011 at 12:03 PM, Stephen Woodbridge wood...@swoodbridge.com wrote: On 11/29/2011 12:42 PM, Stephen Frost wrote: * Stephen Woodbridge (wood...@swoodbridge.com) wrote: I currently have some lists of names that are converted to optimized pcre regular expressions. I uses these to help separate the street from the city name. The lists are only used to create header files that contain the regular expressions that get compiled into the code. The idea being that these names are reasonably static for a given data set. Ah, ok, I see. When converting this to a PG function, I'd probably want to go ahead and pull those lists from the TIGER data set and compile the regexps on PG backend startup instead. Does it handle misspelled names or do any kind of sounds like searching on the city names? I'm guessing 'no', but figured I'd ask anyway.. The lists that I have generated are pulled from a number of sources, like the actual tiger data, the fips 4-2 placenames, I also have some common abbreviations, and misspellings, but it is not doing any sounds like searching. I think that I broke the regular expressions into separate state specific regular expressions because putting them all into a sine expression exceeded some limit in pcre. The regex expressions are created in perl and are highly optimized. You probably can not read the regex's and make much sense out of them, but they are extremely efficient to evaluate. Also you can take just that directory from PAGC and build it and it should create a command line executable that you can test with and run it in the debugger and valgrind, etc. Something like: cd parseaddress ./configure make ./parseaddress 101 W MLK AVE NORTH CHELMSFORD MA 01863 -Steve __**_ postgis-users mailing list postgis-users@postgis.**refractions.netpostgis-users@postgis.refractions.net http://postgis.refractions.**net/mailman/listinfo/postgis-**usershttp://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] Batch geocoding (~2 mil addresses)
FYI. You can not do a COMMIT in a plpgsql or any stored procedures because the the function call is run in a transaction. The way to do this is in an external script like Perl or PHP where you cycle through 500-1000 addresses and updates and then do the COMMIT. -Steve On 12/1/2011 9:04 AM, Ravi ada wrote: Thanks all for the suggestions. I am migrating this process to a bigger machine which has 16GB RAM and faster hard drives. I will incorporate these changes once the migration completes. There are two things I got so far I need to do. 1) use pgScript to commit every 500 or 1000 updates 2) send explain analyze to http://explain.depesz.com/ further evaluate the query plan I will keep you posted guys.. Thanks Ravi Ada -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Andy Colson Sent: Wednesday, November 30, 2011 8:54 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] Batch geocoding (~2 mil addresses) On 11/29/2011 11:18 PM, Ravi ada wrote: Hello All, I have been fighting with PostGIS for the last week or two to geocode about 2 million addresses. Here are the challenges that I am facing. 1)I sorted the addresses by zip, address, city to group the similar addresses together. 2)So far I have seen only 50% of rating zero, that means perfect match. 3)Some addresses have Suite# or Apt# in address line 1. Geocode function takes longer time for such addresses 4)Addresses that are on the interstate, may be on the service road. These are taking longer to code. How about posting an EXPLAIN ANALYZE to http://explain.depesz.com/. Maybe we can speed it up. -Andy ___ 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] Raster pixel value
This is great! Fantastic. I will do some testing. 2011/12/1 Tom van Tilburg tom.van.tilb...@gmail.com Pierre, This is great you're working on this. It is exactly what I think that pgraster is going to make a great tool. Last days I didn't really have time to check out things, hopefully this week. Cheers, Tom On 30-11-2011 19:19, Pierre Racine wrote: Andreas, Tom, I have put two new functions you might be interested by in http://trac.osgeo.org/postgis/**browser/trunk/raster/scripts/**plpgsqlhttp://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql The first one, st_areaweightedsummarystats.**sql, is usefull when you want to compute summary stats (like the weighted mean) of values coming from a raster table for a series of polygons and that you use ST_Intersection(raster, geometry) to compute the intersection. You normally use it this way: SELECT gt.id, (aws).count, (aws).distinctcount, (aws).geom, (aws).totalarea, (aws).meanarea, (aws).totalperimeter, (aws).meanperimeter, (aws).weightedsum, (aws).weightedmean, (aws).maxareavalue, (aws).minareavalue, (aws).maxcombinedareavalue, (aws).mincombinedareavalue, (aws).sum, (aws).mean, (aws).max, (aws).min FROM (SELECT ST_AreaWeightedSummaryStats(**gv) aws FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv FROM rasttable rt, geomtable gt WHERE ST_Intersects(rt.rast, gt.geom) ) foo GROUP BY gt.id ) foo2 The second one, st_summarystatsagg.sql, serve the same purpose but when you are using ST_MapAlgebra() (soon ST_Clip()) to compute the intersection. you normally use it like this: SELECT (ss).count, (ss).sum, (ss).mean, (ss).min, (ss).max FROM (SELECT ST_SummaryStatsAgg(gv) ss FROM (SELECT ST_Clip(rt.rast, gt.geom) gv FROM rasttable rt, geomtable gt WHERE ST_Intersects(rt.rast, gt.geom) ) foo GROUP BY gt.id ) foo2 This is very equivalent to what you were doing. The ST_Clip() replace the ST_MapAlgebra() part and the ST_SummaryStatsAgg() replace the ST_SummaryStats(ST_Union()) part. This should be faster since it does a ST_Union() less. I'm working on a plpgsql version of ST_Clip(). I will post it very soon. Pierre __**_ postgis-users mailing list postgis-users@postgis.**refractions.netpostgis-users@postgis.refractions.net http://postgis.refractions.**net/mailman/listinfo/postgis-**usershttp://postgis.refractions.net/mailman/listinfo/postgis-users __**_ postgis-users mailing list postgis-users@postgis.**refractions.netpostgis-users@postgis.refractions.net http://postgis.refractions.**net/mailman/listinfo/postgis-**usershttp://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] pgsql2shp
Mark, No, I haven't. Right now I am preparing some material for a course and I need some stable release but I'll keep that in mind. Thanks. On 01/12/2011 11:50, Mark Cave-Ayland wrote: It's had a fairly hefty rewrite ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] shp2pgsql error - current transaction is aborted...
On 12/1/2011 8:04 AM, jdmorgan wrote: Hello, I am attempting utilizing the shp2pgsql utility to import a shapefile.The utility reports that the shapefile is of the type MULTIPOLYGON[2] and then proceeds to through a bunch of errors for each record that it can’t import “current transaction is aborted, commands ignored until end of transaction block”.Any advice on how to get around this would be greatly appreciated. Sincerely, -- JDM @ NEMAC If you are using -S (capital S) (Generate simple geometries instead of MULTI geometries) then that would be a problem. Showing us your command might help. -Andy ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: missing FROM-clause entry for table
On 12/1/2011 7:29 AM, maduako ikechukwu wrote: Hi guys, please could someone help me look at this SQL code, I cant figure out what is wrong with it and it gives me this error, ERROR: missing FROM-clause entry for table i LINE 3: Select I.temp_lst_id,I.temp_value as I_t, R.tv.val as R_t, (... ^ Here is the code; Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t From ( Select ST_intersection(R.rast,I.the_geom) AS tv From in_situ_lst I, lst_day R Where I.the_geom R.rast AND ST_intersects(R.rast,I.the_geom) And I.temp_lst_id = 2 )foo; There is an outside statement, and an inside. The outside: Select I.temp_value as I_t, R.tv.val as R_t, (I_t - R_t) as D_t is trying to use a table alias from the inside. You can't do that. Or said another way, you are using derived tables. The inside select statement is run first, and the resultset is called foo: Select ST_intersection(R.rast,I.the_geom) AS tv From in_situ_lst I, lst_day R Where I.the_geom R.rast AND ST_intersects(R.rast,I.the_geom) And I.temp_lst_id = 2 The outside then does select [stuff] from foo. The outside cannot see any table's named I. It can only see foo. -Andy ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] shp2pgsql error - current transaction is aborted...
On 12/1/2011 9:56 AM, Andy Colson wrote: On 12/1/2011 8:04 AM, jdmorgan wrote: Hello, I am attempting utilizing the shp2pgsql utility to import a shapefile.The utility reports that the shapefile is of the type MULTIPOLYGON[2] and then proceeds to through a bunch of errors for each record that it can’t import “current transaction is aborted, commands ignored until end of transaction block”.Any advice on how to get around this would be greatly appreciated. Sincerely, -- JDM @ NEMAC If you are using -S (capital S) (Generate simple geometries instead of MULTI geometries) then that would be a problem. Showing us your command might help. -Andy Here is the command I was using shp2pgsql -s 4326 FHM_ADS_2003_4326.shp public.FHM_ADS_2003 | psql -d pgtest1 -U mbp I have figured out the issue by looking at the SQL generated by shp2pgsql. As it turns out there were null in a date field in the shape file, and postgresql didn't like that for the insert statement. Thanks, -- JDM Morgan @ NEMAC ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Error on updating geometry column from two columns value
ST_GeomFromText turns WKT into a geometry, not SQL. You can either
format WKT (text) using:
ST_GeomFromText('POINT(' || NEW.koordinat_x || ', ' || NEW.koordinat_y
|| ')', 32748)
Or, a simpler/faster/lossless geometry constructor would be to pass
the floating point values directly to a point geometry:
ST_SetSRID(ST_MakePoint(NEW.koordinat_x, NEW.koordinat_y), 32748)
Note: you had your X/Y coordinates the other way around. PostGIS
coordinates are always ordered X/Y or long/lat.
-Mike
On 1 December 2011 20:31, Firman Hadi jalmibur...@gmail.com wrote:
Dear all,
I want to create one table with 3 columns (x, y, geom). I want to input the
x and y using form with PHP.
When I submit the form, it will trigger the new row.
I use this step below but when I insert the data I get the error as in
attachment.
I hope that anyone can help me to solve the problem.
Thank you in advance.
Kind regards,
Firman Hadi
Center for Remote Sensing - ITB
Indonesia
CREATE TABLE try_geometry (
koordinat_y integer,
koordinat_x integer,
geom geometry PRIMARY KEY
);
CREATE FUNCTION try_geometry_func () RETURNS trigger AS '
BEGIN
NEW.geometri = ST_GeomFromText('POINT(NEW.koordinat_y,
NEW.koordinat_x)',32748);
RETURN NEW;
END;
' LANGUAGE plpgsql;
===
CREATE TRIGGER try_geom_trg BEFORE INSERT OR UPDATE
ON coba FOR EACH ROW
EXECUTE PROCEDURE try_geometry_func ();
___
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] Error on updating geometry column from two columns value
Dear Mike,
Thank you for the solutions. I will try it.
Thanks again.
Cheers,
Firman.
On 12/2/11 5:16 AM, Mike Toews wrote:
ST_GeomFromText turns WKT into a geometry, not SQL. You can either
format WKT (text) using:
ST_GeomFromText('POINT(' || NEW.koordinat_x || ', ' || NEW.koordinat_y
|| ')', 32748)
Or, a simpler/faster/lossless geometry constructor would be to pass
the floating point values directly to a point geometry:
ST_SetSRID(ST_MakePoint(NEW.koordinat_x, NEW.koordinat_y), 32748)
Note: you had your X/Y coordinates the other way around. PostGIS
coordinates are always ordered X/Y or long/lat.
-Mike
On 1 December 2011 20:31, Firman Hadijalmibur...@gmail.com wrote:
Dear all,
I want to create one table with 3 columns (x, y, geom). I want to input the
x and y using form with PHP.
When I submit the form, it will trigger the new row.
I use this step below but when I insert the data I get the error as in
attachment.
I hope that anyone can help me to solve the problem.
Thank you in advance.
Kind regards,
Firman Hadi
Center for Remote Sensing - ITB
Indonesia
CREATE TABLE try_geometry (
koordinat_y integer,
koordinat_x integer,
geom geometry PRIMARY KEY
);
CREATE FUNCTION try_geometry_func () RETURNS trigger AS '
BEGIN
NEW.geometri = ST_GeomFromText('POINT(NEW.koordinat_y,
NEW.koordinat_x)',32748);
RETURN NEW;
END;
' LANGUAGE plpgsql;
===
CREATE TRIGGER try_geom_trg BEFORE INSERT OR UPDATE
ON coba FOR EACH ROW
EXECUTE PROCEDURE try_geometry_func ();
___
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
