[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] restore database
From: "Paragon Corporation" <lr () pcorp ! us>
Date: 2010-04-26 7:44:35
Message-ID: 81AF6018C1D440DB81F58CE1D9DFEEBF () J
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Marko,
Can't think of why number of points would make a difference if it showed up
before. That could mean a bug of some sort in newer PostGIS in which case,
it would be great to get a bug report with an attachment of the offending
table.
Before that though - lets rule out some non-bug possibilities.
When you restored -- did you restore to a new version of PostGIS or same
version and which version of PostGIS are you running now and before?
One common possibility is that the spatial_ref_sys record is not registered
for the srid of this table. Since QGIS/FME are srid aware, they might not
be showing it because its off in la la land to them.
What often causes this behavior is if you restored on top of a new PostGIS
template database any custom records that were added/modified to
spatial_ref_sys may be lost. To rule this out
1) What is the srid of this table and verify it exists in spatial_ref_sys
2) If it does exist, do a SELECT proj4text from spatial_ref_sys where srid =
srid_here on both the old working database and the new database and verify
they return the same answer.
If that isn't the issue
To rule point out, you can dump the data into a new table and simplify it to
reduce number of points
http://www.postgis.org/documentation/manual-svn/ST_SimplifyPreserveTopology.
html
SELECT gid, ST_SimplifyPreserveTopology(the_geom, 1000) As the_geom
INTO newtable
FROM badtable
The 1000 is in units of spatial coordinate (so if your data is in long lat
use something like 0.01 or something instead)
The count of your points in your new table should then be much lower. If
that still doesn't display then not a number of points issue. Either way if
its a number of points or some other issue, would be nice to get a bug
report.
http://trac.osgeo.org/postgis/newticket
which you will need an osgeo account -- http://www.osgeo.org/osgeo_userid
if you don't have one already.
Thanks,
Regina and Leo
http://www.postgis.us
_____
From: postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Marko
Cubranic
Sent: Monday, April 26, 2010 3:16 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] restore database
Leo and Regina,
I have verified my table , and table contanis one object geometry (about 40
000 points).
Maybe the problem is in too large object.
Marko
2010/4/23 Paragon Corporation <lr@pcorp.us>
Marko,
Not sure off hand what could be wrong here. Did you verify the table you
can't visualize has data?
Its possible the data didn't load or didn't get backed up because it had a
very invalid geometry in it like a polygon with two few points. This issue
was fixed in PostGIS 1.4.2 and PostGIS 1.5.1. Before it was possible that
you could create a record in PostGIS that was clearly invalid and you would
no longer be able to export it out or reimport it. We are guessing that
might be the issue here.
Leo and Regina
http://www.postgis.us
_____
From: postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of Marko
Cubranic
Sent: Friday, April 23, 2010 3:27 AM
To: postgis-users@postgis.refractions.net
Subject: [postgis-users] restore database
Dear,
i have made backup of spatial database and after i v restored it i couldn't
visualize
one geometry table in Qgis, uDIG,FME-viewer.Before i did backup of database
i could visualize all data.
I did backup and restore on same computer with PostgreSQL 8.3 installed.
Database consists of 7 geometry tables, which 2 of geometry tables are
loaded from shapefile, and one of those two
cannot be visualized after backup and restore.
So if anyone can help, thank you.
--
Marko Èubraniæ
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Marko Èubraniæ
[Attachment #5 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-2" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7600.16535"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>Marko,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>Can't think of why number of points would make a difference if
it showed up before. That could mean a bug of some sort in newer PostGIS
in which case, it would be great to get a bug report with an attachment of the
offending table. </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>Before that though - lets rule out some non-bug
possibilities.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>When you restored -- did you restore to a new version of
PostGIS or same version and which version of PostGIS are you running now
and before?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><SPAN
class=075232707-26042010><FONT color=#0000ff size=2 face=Arial>One
common possibility is that the spatial_ref_sys record is not registered for
the srid of this table. Since QGIS/FME are srid aware, they might not be
showing it because its off in la la land to them.
</FONT></SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><SPAN
class=075232707-26042010><FONT color=#0000ff size=2
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><SPAN
class=075232707-26042010><FONT color=#0000ff size=2 face=Arial>What often
causes this behavior is if you restored on top of a new PostGIS template
database any custom records that were added/modified to spatial_ref_sys may be
lost. To rule this out</FONT></SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><SPAN
class=075232707-26042010><FONT color=#0000ff size=2
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><SPAN
class=075232707-26042010><FONT color=#0000ff size=2 face=Arial>1) What is the
srid of this table and verify it exists in
spatial_ref_sys</FONT></SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><SPAN
class=075232707-26042010><FONT color=#0000ff size=2 face=Arial>2) If it does
exist, do a SELECT proj4text from spatial_ref_sys where srid = srid_here
on both the old working database and the new database and verify they return the
same answer.</FONT></SPAN></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><SPAN
class=075232707-26042010><FONT color=#0000ff size=2
face=Arial></FONT></SPAN></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>If that isn't the issue</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial> </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>To rule point out, you can dump the data into a new table and
simplify it to reduce number of points</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.org/documentation/manual-svn/ST_SimplifyPreserveTopology.html \
">http://www.postgis.org/documentation/manual-svn/ST_SimplifyPreserveTopology.html</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>SELECT gid, ST_SimplifyPreserveTopology(the_geom, 1000) As
the_geom</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>INTO newtable</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>FROM badtable</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>The 1000 is in units of spatial coordinate (so if your
data is in long lat use something like 0.01 or something
instead)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>The count of your points in your new table should then be much
lower. If that still doesn't display then not a number of points issue.
Either way if its a number of points or some other issue, would be nice to get a
bug report.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial><A
href="http://trac.osgeo.org/postgis/newticket">http://trac.osgeo.org/postgis/newticket</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>which you will need an osgeo account -- <A
href="http://www.osgeo.org/osgeo_userid">http://www.osgeo.org/osgeo_userid</A>
if you don't have one already.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>Thanks,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial>Regina and Leo</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=075232707-26042010><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Marko
Cubranic<BR><B>Sent:</B> Monday, April 26, 2010 3:16 AM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] restore
database<BR></FONT><BR></DIV>
<DIV></DIV>Leo and Regina,<BR><BR>I have verified my table , and table contanis
one object geometry (about 40 000 points).<BR>Maybe the problem is in too large
object.<BR><BR>Marko<BR><BR>
<DIV class=gmail_quote>2010/4/23 Paragon Corporation <SPAN dir=ltr><<A
href="mailto:lr@pcorp.us">lr@pcorp.us</A>></SPAN><BR>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; \
PADDING-LEFT: 1ex" class=gmail_quote>
<DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial>Marko,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>Not sure
off hand what could be wrong here. Did you verify the table you can't
visualize has data?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>Its
possible the data didn't load or didn't get backed up because it had a very
invalid geometry in it like a polygon with two few points. This issue
was fixed in PostGIS 1.4.2 and PostGIS 1.5.1. Before it was possible
that you could create a record in PostGIS that was clearly invalid and you
would no longer be able to export it out or reimport it. We are guessing
that might be the issue here.</FONT></SPAN></DIV>
<DIV class=im>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>Leo and
Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial><A
href="http://www.postgis.us"
target=_blank>http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV><BR></DIV>
<DIV dir=ltr lang=en-us align=left>
<HR>
<FONT size=2 face=Tahoma><B>From:</B> <A
href="mailto:postgis-users-bounces@postgis.refractions.net"
target=_blank>postgis-users-bounces@postgis.refractions.net</A> [mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net"
target=_blank>postgis-users-bounces@postgis.refractions.net</A>] <B>On Behalf
Of </B>Marko Cubranic<BR><B>Sent:</B> Friday, April 23, 2010 3:27
AM<BR><B>To:</B> <A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><B>Subject:</B>
[postgis-users] restore database<BR></FONT><BR></DIV>
<DIV>
<DIV></DIV>
<DIV class=h5>
<DIV></DIV>Dear,<BR><BR>i have made backup of spatial database and after i v
restored it i couldn't visualize <BR>one geometry table in Qgis,
uDIG,FME-viewer.Before i did backup of database i could visualize all
data.<BR>I did backup and restore on same computer with PostgreSQL 8.3
installed.<BR>Database consists of 7 geometry tables, which 2 of geometry
tables are loaded from shapefile, and one of those two<BR>cannot be visualized
after backup and restore. <BR><BR>So if anyone can help, thank you.<BR><BR><BR
clear=all><BR>-- <BR>Marko
Èubraniæ<BR></DIV></DIV></DIV><BR>_______________________________________________<BR>postgis-users \
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR></BLOCKQUOTE></DIV><BR><BR \
clear=all><BR>-- <BR>Marko Èubraniæ<BR></BODY></HTML>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic