[postgis-users] Problem refreshing table list

George Washington gws293 at hotmail.com
Sun May 8 18:46:02 PDT 2011 



Thanks Michael, the lack of a geometry_columns entry for my table was indeed the problem.
Regards
Victor
> 
> Message: 9
> Date: Sun, 08 May 2011 12:42:26 +0200
> From: Micha?l Michaud <michael.michaud at free.fr>
> Subject: Re: [postgis-users] Problem refreshing table list
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <4DC67392.3080405 at free.fr>
> Content-Type: text/plain; charset="iso-8859-1"; Format="flowed"
> 
> Hi George,
> 
> As far as OpenJUMP is concerned, the problem could be that the 
> geometry_columns table is not updated, depending on how you created your 
> tables under PostgreSQL/PostGIS.
> Using addGeometryColumn() function when you create your tables should 
> update the geometry_columns table automagically.
> 
> In OpenJUMP, while using Open > Database menu, only tables available in 
> geometry_columns are displayed
> However, using Layer > Execute SQL query let you query your database 
> even if geometry_columns table is empty
> 
> Hope that helps
> 
> Micha?l
> 
> 
> Le 08/05/2011 12:04, George Washington a ?crit :
> > Hi, I have a strange problem no doubt the result of my lack of 
> > knowledge. Using Postgresql 9.3/PostGIS 1.5.2 under Windows 7 32-bit 
> > when I look at a database list of tables using PGAdminIII or 
> > PhpPgAdmin all is well. But when I use OpenJump or Udig all I see is 
> > an old list of tables. Changes which are reflected in PGAdminIII or 
> > PhpPgAdmin are not seen by the other two client programs. Restarting 
> > the server makes no difference.
> > What am I missing?
> > Many thanks for your help
> > George
> > ------------------------------------------------------------------------
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20110508/b906cbd6/attachment-0001.html>
> 
> ------------------------------
> 
> Message: 10
> Date: Sun, 8 May 2011 10:32:57 -0400
> From: Stephen Frost <sfrost at snowman.net>
> Subject: Re: [postgis-users] Geocoder (from extras)
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <20110508143257.GZ4548 at tamriel.snowman.net>
> Content-Type: text/plain; charset="us-ascii"
> 
> * Paragon Corporation (lr at pcorp.us) wrote:
> >  I'm not sure how much preplanning will help.  Because plans are cached in
> > PostgreSQL for these dynamic like queries and I think they are actually
> > cached across sessions using shared memory.   So upping shared memory helps
> > quite a bit.
> 
> We're using execute though, so the main query isn't cache'd at all..
> 
> 	Stephen
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: application/pgp-signature
> Size: 198 bytes
> Desc: Digital signature
> URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20110508/f25d09ac/attachment-0001.bin>
> 
> ------------------------------
> 
> Message: 11
> Date: Sun, 8 May 2011 10:36:37 -0400
> From: Stephen Frost <sfrost at snowman.net>
> Subject: Re: [postgis-users] Geocoder (from extras)
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <20110508143637.GA4548 at tamriel.snowman.net>
> Content-Type: text/plain; charset="us-ascii"
> 
> Stephen,
> 
> * Stephen Woodbridge (woodbri at swoodbridge.com) wrote:
> > In a geocoder I wrote in 2000, I mapped city, state and/or zipcode
> > into a list of counties, then only searched those counties. This
> > greatly reduced the 3300 counties in Tiger to a small handful that
> > needed to be searched. The FIPS-4 document has a mapping of all
> > place names to counties. Zipcodes are easier to handle because you
> > can just index the records with zipcodes but if you wanted to widen
> > the search, then map the zipcode to a place name and then to
> > counties.
> 
> Yeah, the geocoder does something similar to this (as I recall, we
> create a list of zip codes to scan through, since that's indexed and the
> tables aren't stored per-county in my setup).
> 
> 	Thanks,
> 
> 		Stephen
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: application/pgp-signature
> Size: 198 bytes
> Desc: Digital signature
> URL: <http://postgis.refractions.net/pipermail/postgis-users/attachments/20110508/b3ca5f09/attachment-0001.bin>
> 
> ------------------------------
> 
> Message: 12
> Date: Sun, 8 May 2011 13:14:28 -0400
> From: "Paragon Corporation" <lr at pcorp.us>
> Subject: Re: [postgis-users] Geocoder (from extras)
> To: "'PostGIS Users Discussion'"
> 	<postgis-users at postgis.refractions.net>
> Message-ID: <8FE3787DA8AA4F8085E4CF34AD9F571D at J>
> Content-Type: text/plain;	charset="us-ascii"
> 
> Yah it does.  Though I think I did break it out for simplicity because they
> were no longer updating that zip500 or whatever that file was on census
> anymore.  So I opted to store in separate state schemas and build
> differently.  I'm not sure that adds much overhead anyway.
> 
> One thing I did notice with the code which we are cleaning up is that all
> the functions come in as VOLATILE because they have no markers on them.
> Most should be STABLE or IMMUTABLE.  Also some functions that should have
> been implemented as SQL were implemented as plpgsql.
> 
> We are changing all of these.  This should make most difference with batch
> geocoding.  Because for example for those small mini queries where you have
> all addresses on the same street, that can be cached since they will all use
> the same result of that subquery.
> 
> Right now they can't because that piece is marked volatile.
> 
> I've committed some of these changes already, but still need to test on some
> large data sets.  If anyone wants to help test those out, that would be
> create.
> 
> 
> To upgrade your scripts -- rerun the create_geocode.sql .  That will allow
> you to keep your data but replace the scripts.
> 
> As far as plans go, some statements are best not cached.  That would
> probably require some experimentation.  
> 
> Steve W.  Note that most of the queries are parameterized except I think for
> a couple.  For those couple I'm not sure it makes sense to plan them since
> the plan would be wrong for subsequent.  If as you said it you change it to
> break out to separate tables wirting 52 someodd queries, then the plan would
> be valid.
> 
> Right now I feel the low hanging fruit is the actually cacheability of the
> results. 
> 
> I'm also noticing the normalize_address takes much longer that I would
> expect it to.  For some its like 50% of the time.  I'm troubleshooting why
> that is because that function doesn't touch too many tables (if any) besides
> the ones we include and load as part of tiger geocoder.
> 
> Thanks,
> Regina
> http://www.postgis.us
> 
> 
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephen
> Frost
> Sent: Sunday, May 08, 2011 10:37 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Geocoder (from extras)
> 
> Stephen,
> 
> * Stephen Woodbridge (woodbri at swoodbridge.com) wrote:
> > In a geocoder I wrote in 2000, I mapped city, state and/or zipcode 
> > into a list of counties, then only searched those counties. This 
> > greatly reduced the 3300 counties in Tiger to a small handful that 
> > needed to be searched. The FIPS-4 document has a mapping of all place 
> > names to counties. Zipcodes are easier to handle because you can just 
> > index the records with zipcodes but if you wanted to widen the search, 
> > then map the zipcode to a place name and then to counties.
> 
> Yeah, the geocoder does something similar to this (as I recall, we create a
> list of zip codes to scan through, since that's indexed and the tables
> aren't stored per-county in my setup).
> 
> 	Thanks,
> 
> 		Stephen
> 
> 
> 
> 
> ------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> End of postgis-users Digest, Vol 109, Issue 8
> *********************************************
 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110509/c2811e50/attachment.html>

More information about the postgis-users mailing list