Here is Paul Ramsey's solution for that type of problem: http://blog.cleverelephant.ca/2010/09/postgis-back-up-restore.html
Worth Lutz -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Pedro Costa Sent: Thursday, March 14, 2013 8:44 AM To: [email protected] Subject: Re: [mapserver-users] postgis layers don't show up I found the problem... I don't know why but the problem is bucause i make a dump from database with postgis 1.5 and in new databse i have the postgis 2... so the problem its the dump and restore.... If i make the same query in database with postgis 1.5 i got some records.... Em 14-03-2013 09:56, Pedro Costa escreveu: > Very strange...I got nothing again: > > select st_astext(st_extent(the_geom)) from ruas: > RESULT: > > "POLYGON((88386.2413216648 122184.527972102,88386.2413216648 > 124022.075913784,91216.2575858403 124022.075913784,91216.2575858403 > 122184.527972102,88386.2413216648 122184.527972102))" > > > select count(*) > from ruas > where the_geom && GeomFromText > ('POLYGON((88386.2413216648 122184.527972102,88386.2413216648 > 124022.075913784,91216.2575858403 124022.075913784,91216.2575858403 > 122184.527972102,88386.2413216648 122184.527972102))',27492); > > > RESULT: 0 > > > > > > > > Em 13-03-2013 18:27, Stephen Woodbridge escreveu: >> On 3/13/2013 1:49 PM, Pedro Costa wrote: >>> Em 13-03-2013 17:39, Stephen Woodbridge escreveu: >>>> So What do you get in pgadmin or psql with: >>>> >>>> select count(*) >>>> from ruas >>>> where the_geom && GeomFromText('POLYGON((88071.2044330519 >>>> 122184.53,88071.2044330519 124022.08,91531.2955669481 >>>> 124022.08,91531.2955669481 122184.53,88071.2044330519 >>>> 122184.53))',27492); >>> >>> nothing...count = 0... >> >> This means there is some kind of disconnect between the data in the >> database and how you have it defined in the mapfile. >> >>>> >>>> select st_astext(st_extents(st_collect(the_geom))) from ruas; >>> >>> Previously i have compared the result of 'st_extent' with extent in >>> mapfile and its the same.... >>> Your query with st_collects give me an error (ERROR: aggregate function >>> calls cannot be nested).... >> >> Try: >> >> select st_astext(st_extents(the_geom)) from ruas; >> >> Once you get this you will have the min and max extents of the data >> in the database and you can compare that to the polygon above and see >> if it overlaps. >> >> -Steve >> >> >>>> >>>> -Steve >>>> >>>> >>>> >>>> On 3/13/2013 1:22 PM, Pedro Costa wrote: >>>>> Yes, i confirmed that right now, its the bb but i don't understand >>>>> the >>>>> problem because in other server (with the same coordinates) this >>>>> works >>>>> fine. >>>>> i'm also confirmed the extent in qgis and its correct... >>>>> >>>>> >>>>> my mapfile top data: >>>>> >>>>> EXTENT 88386.2 122329.41 91216.3 124022.08 >>>>> UNITS meters >>>>> SIZE 500 500 >>>>> SHAPEPATH "shape" >>>>> SYMBOLSET "../common/symbols/symbols-pmapper.sym" >>>>> FONTSET "../common/fonts/msfontset.txt" >>>>> RESOLUTION 96 >>>>> IMAGETYPE png >>>>> INTERLACE OFF >>>>> >>>>> PROJECTION >>>>> "init=epsg:27493" >>>>> >>>>> END >>>>> >>>>> >>>>> >>>>> my layer: >>>>> >>>>> >>>>> LAYER >>>>> NAME "ruas" >>>>> STATUS DEFAULT >>>>> DEBUG 5 >>>>> TYPE LINE >>>>> CONNECTIONTYPE postgis >>>>> PROCESSING "CLOSE_CONNECTION=DEFERl" >>>>> CONNECTION "user=pedro password=****** dbname=norte >>>>> host=localhost >>>>> options='-c client_encoding=LATIN1'" >>>>> DATA "the_geom from ruas using unique n_rua using srid=27493" >>>>> TOLERANCE 3 >>>>> TOLERANCEUNITS pixels >>>>> TEMPLATE void >>>>> PROJECTION >>>>> "init=epsg:27493" >>>>> END >>>>> METADATA >>>>> "DESCRIPTION" "Ruas" >>>>> "RESULT_FIELDS" "id" >>>>> "RESULT_HEADERS" "id" >>>>> >>>>> "ows_title" "Ruas " >>>>> END # Metadata >>>>> CLASS >>>>> Name 'ruas' >>>>> COLOR 0 0 0 >>>>> END # Class >>>>> >>>>> END >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> Em 13-03-2013 17:14, Stephen Woodbridge escreveu: >>>>>> The polygon is the bounding box of the image that you requested. >>>>>> Mapserver only needs the geometries the cross of the map image and >>>>>> this is what the where clause is doing. >>>>>> >>>>>> Why do you thin this is not correct? What does the top of you >>>>>> mapfile >>>>>> look like and what does this LAYER definition look like? >>>>>> >>>>>> -Steve W >>>>>> >>>>>> On 3/13/2013 1:12 PM, Pedro Costa wrote: >>>>>>> Thanks to all. >>>>>>> The problem is the query.despite don't give me any error returns 0 >>>>>>> features... >>>>>>> This happens because the 'where with the polygon' [1]... >>>>>>> Anybody knows the origin of that polygon? >>>>>>> >>>>>>> >>>>>>> select encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') as >>>>>>> geom,"n_rua" >>>>>>> from ruas >>>>>>> where the_geom && GeomFromText('POLYGON((88071.2044330519 >>>>>>> 122184.53,88071.2044330519 124022.08,91531.2955669481 >>>>>>> 124022.08,91531.2955669481 122184.53,88071.2044330519 >>>>>>> 122184.53))',27492) >>>>>>> >>>>>>> >>>>>>> >>>>>>> Em 13-03-2013 16:54, Carlo Pelliconi escreveu: >>>>>>>> I think like others that the problem could be in srid definition. >>>>>>>> >>>>>>>> Remember that you need to define an srid for the mapfile and an >>>>>>>> srid >>>>>>>> for each layer in the mapfile. >>>>>>>> When using postgis connectiontype I also put "using >>>>>>>> srid=yourPostGisLayerSrid" in the DATA sql statement of the layer >>>>>>>> (mandatory? I don't know, but it works) >>>>>>>> >>>>>>>> Hope this helps. >>>>>>>> Best Regards, Carlo >>>>>>>> >>>>>>>> Il 13/03/2013 12:40, Pedro Costa ha scritto: >>>>>>>>> Hi guys, >>>>>>>>> >>>>>>>>> I have one mapfile with conections to postgis layers and one >>>>>>>>> raster. >>>>>>>>> My problem is that the layers from postgis don't show up...If >>>>>>>>> i make >>>>>>>>> the request without the raster i got only white images... >>>>>>>>> I have the debug level in 5 and I got no erros in all logs >>>>>>>>> (including >>>>>>>>> apache)... >>>>>>>>> The permissions of the database are correct because I tried >>>>>>>>> accessing >>>>>>>>> with qgis and it worked fine. I'm also try specified the >>>>>>>>> projection >>>>>>>>> ("using srid = 27493") but without sucess... >>>>>>>>> I'm also tried add one of the Iayers of db but from a shape and >>>>>>>>> worked fine... >>>>>>>>> Furthermore, very strange is that when I try to run mapfile with >>>>>>>>> pmapper, I can search for attributes and zoom to the correct >>>>>>>>> areas >>>>>>>>> (despite can't see geometries) ... >>>>>>>>> >>>>>>>>> Anybody have an idea? >>>>>>>>> >>>>>>>>> Thanks >>>>>>>>> >>>>>>>>> >>>>>>>>> SO: Ubuntu server >>>>>>>>> MapServer version 6.0.1 >>>>>>>>> _______________________________________________ >>>>>>>>> mapserver-users mailing list >>>>>>>>> [email protected] >>>>>>>>> http://lists.osgeo.org/mailman/listinfo/mapserver-users >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> _______________________________________________ >>>>>>> mapserver-users mailing list >>>>>>> [email protected] >>>>>>> http://lists.osgeo.org/mailman/listinfo/mapserver-users >>>>>> >>>>>> _______________________________________________ >>>>>> mapserver-users mailing list >>>>>> [email protected] >>>>>> http://lists.osgeo.org/mailman/listinfo/mapserver-users >>>>> >>>>> _______________________________________________ >>>>> mapserver-users mailing list >>>>> [email protected] >>>>> http://lists.osgeo.org/mailman/listinfo/mapserver-users >>>> >>>> _______________________________________________ >>>> mapserver-users mailing list >>>> [email protected] >>>> http://lists.osgeo.org/mailman/listinfo/mapserver-users >>> >>> _______________________________________________ >>> mapserver-users mailing list >>> [email protected] >>> http://lists.osgeo.org/mailman/listinfo/mapserver-users >> >> _______________________________________________ >> mapserver-users mailing list >> [email protected] >> http://lists.osgeo.org/mailman/listinfo/mapserver-users > _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users ----- No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.2904 / Virus Database: 2641/6169 - Release Date: 03/13/13 _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
