[Mapserver-users] RE: [postgis-users] RE: gone are my queryresults
Charlton Purvis
cpurvis at asg.sc.edu
Sun May 2 23:05:21 EDT 2004
Let's hear it for the Canadians!!!
FabulOUS, Paul. You're an absolute life savior / saver / whatever!
Talk about premature graying. That was an unpleasant couple of days, but it was solved by binding the UNIQUE to a home-grown column, and now everything is golden.
Whew!
Infinte thanks. So . . . are you interested in inheriting Alaska? Maybe I can talk Dubya into a little trade since I owe you big time.
How about we start off w/ a case or two (thousand) of beer?
Charlton
-----Original Message-----
From: Paul Ramsey [mailto:pramsey at refractions.net]
Sent: Sun 5/2/2004 12:45 AM
To: Administrator at netnation.refractions.net
Cc: mapserver-users at lists.gis.umn.edu; PostGIS Users Discussion
Subject: Re: [Mapserver-users] RE: [postgis-users] RE: gone are my queryresults
Got your back, Charlton :)
The problem is that your PostgreSQL oids are larger than the mapserver
row number variable can store. The oid is larger than a signed 32 bit
integer can store.
So, solution.
Do not use oid as your "unique" field. Make use of the "using unique"
clause in your data statement, and reference a different unique field
in your table. Make sure that field is indexed. If you have loaded your
data with shp2pgsql, you probably have a 'gid' field you can use that
is a primary key (and therefore already indexed).
Yours,
Paul
On Saturday, May 1, 2004, at 08:42 PM, Charlton Purvis wrote:
> OK, Greg, I think we might be onto something. But it still looks very
> grim.
>
> I turned on all the logging, and I notice that MS sends it this query:
>
> LOCATION: pg_parse_query, postgres.c:464
> LOG: 00000: statement: DECLARE mycursor BINARY CURSOR FOR SELECT
> temperature_celcius::text,asbinary(force_collection(force_2d(the_geom))
> ,'NDR'),OID::text from sst_map WHERE
> (date_trunc('hour',report_time_stamp + interval '1 hour' * -04) =
> date_trunc('hour',timestamp without time zone '2004-05-01 22:00:00')
> and (label_z <= 5 or label_z is null)) and (the_geom && setSRID(
> 'BOX3D(-83.0454044480208 24.3838940085927,-82.6541628156105
> 24.775135641003)'::BOX3D,find_srid('','sst_map','the_geom') ))
>
> OK. That's good. Because if I stick the SELECT part at a psql
> prompt, I get back the row I want. And I also notice that its OID is
> 2185295089.
>
> But if I look at the last sql statement in the log, it's another
> cursor, but the OID is wrong.
>
> LOCATION: pg_parse_query, postgres.c:464
> LOG: 00000: statement: DECLARE mycursor BINARY CURSOR FOR SELECT
> station_id::text,time_stamp::text,z::text,label_z::text,temperature_cel
> cius::text,temperature_fahrenheit::text,value_temperature_celcius::text
> ,value_temperature_fahrenheit::text,lon::text,lat::text,title::text,ins
> titution::text,institution_url::text,institution_dods_url::text,source:
> :text,refs::text,contact::text,report_time_stamp::text,asbinary(force_c
> ollection(force_2d(the_geom)),'NDR') from sst_map WHERE OID =
> 2147483647
>
> So had they kept the OID, I would be in good shape.
>
> This isn't looking good!!!
>
> Charlton
>
> -----Original Message-----
> From: Gregory S. Williamson [mailto:gsw at globexplorer.com]
> Sent: Sat 5/1/2004 11:11 PM
> To: PostGIS Users Discussion; mapserver-users at lists.gis.umn.edu
> Cc:
> Subject: RE: [postgis-users] RE: gone are my query results
>
>
>
>
> If you turn on the logging of statements in postgres and run the
> mapserver side of things, does the query show up in the logs ?
>
> If the query does not appear then I'd suspect networking or
> permissions or some darn thing keeping postgres from ever seeing the
> query.
>
> It it does show up then maybe the problem is in the return trip to MS
> or in the processing of that data (perhaps your OID suspicions), since
> it sounds as if the query itself can be processed.
>
> Any changes to *anything* on either server and anything in between ?
> (not likely but always worth ruling out)
>
> Or, shot in the dark, something messing with TOLERANCE so no points
> are selected in a query ?
>
> Very puzzling, I agree.
>
> Greg W.
>
> -----Original Message-----
> From: Charlton Purvis on behalf of Charlton Purvis
> Sent: Sat 5/1/2004 7:59 PM
> To: PostGIS Users Discussion; mapserver-users at lists.gis.umn.edu
> Cc:
> Subject: RE: [postgis-users] RE: gone are my query results
> Thanks for your reply, Gregory. I'm forwarding this onto the MS
> folks to see if they have any pearls of wisdom.
>
> Everything looks great in the db. If I render a map, I see all the
> points correctly. If I take that query and plug it in to the command
> line, I see the rows. But the moment I try to do either a cgi query,
> WFS query, or a PHP query, I get nothing back. No error message.
>
> I'm actually getting panicky which isn't any fun. Has anyone seen
> anything like this before?
>
> Queries rely on OID's and SRID's. Is there any way they could have
> gotten mucked up? Can I somehow refresh them?
>
> Thanks,
>
> Charlton
>
> -----Original Message-----
> From: Gregory S. Williamson [mailto:gsw at globexplorer.com]
> Sent: Sat 5/1/2004 6:20 PM
> To: PostGIS Users Discussion
> Cc:
> Subject: [postgis-users] RE: gone are my query results
>
>
>
>
> If you do the query directly to postgres (via psql or pgAdmin or
> whatever) does the query return results ?
>
> If so, then my guess is that this is not a database error, at least
> not directly. If ot then yes, it would seem that somehow the postGIS
> itself has gottent snarfled.
>
> The change you made relating the geometryColumns doesn't sound to me
> like it should effect queries unless somehow the SRID info for the
> tables in your mapserver queries got clobbered; should be easy enough
> to confirm that this data is ok.
>
> I am not familiar enough with PHP and mapserver to have a clue; does
> the mapserver log itself show anything ?
>
> Not much help, I'm afraid, but perhaps posting this to the MMS list
> would add some more knowledge to the issue at hand ?
>
> Greg Williamson
> DBA
> GlobeXplorer LLC
>
> -----Original Message-----
> From: Charlton Purvis on behalf of Charlton Purvis
> Sent: Sat 5/1/2004 2:46 PM
> To: PostGIS Users Discussion
> Cc:
> Subject: gone are my query results
> Hi, folks:
>
> Bizarre and unnerving problem here.
>
> Up until last night, mapping was fine and point-query was fine. Now
> it appears that the mapping is still great, but the point query and
> WFS are toast. I don't get any errors in pgsql.log or php.log. I get
> . . . nothing. Even going mapserv cgi query returns empty for a query
> I know should work.
>
> Maybe I'm being taught a lesson. In a separate database, I noticed
> that addgeometrycolumn was taking too long -- I create about 400
> smaller tables per day. So I commented out fix_geometry_columns();
> from the addgeometrycolumn function and did it once I had finished
> creating all my new tables. So am I paying the price? Has this
> corrupted this database as well as my other?
>
> If all this is OID stuff, is there any way to refresh all OID's?
>
> Restarted . . . yes. VACUUM and ANALYZE'd . . . yes.
>
> Thanks,
>
> Charlton
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> Mapserver-users mailing list
> Mapserver-users at lists.gis.umn.edu
> http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>
Paul Ramsey
Refractions Research
Email: pramsey at refractions.net
Phone: (250) 885-0632
_______________________________________________
Mapserver-users mailing list
Mapserver-users at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the mapserver-users
mailing list