[postgis-users] RE: newbie question

[Paul Ramsey]

Yes, you are asking too much of PgAdminIII. It loads the whole SQL  
file into memory, then executes it, so a huge file will kill it.

Use the psql commandline utility instead, to directly pipe the file  
from disk to database.

psql -f myfile.sql -U myusername -d mydatabase



On 31-Jan-08, at 1:17 PM, Thomas Crosslin wrote:

> Thanks to Paul and Stephen, both of your suggestions worked great.
>
> I have a follow up question that Paul may be interested in.
>
>
>
> When working on the below, I loaded three polygon shapefiles to my DB.
>
> I used shp2pgsql to load my shapefiles to a sql file and then  
> loaded and ran the sql through PGAdminIII
>
> I also have a raster grid that I need to query at the same time –  
> but since rasters aren’t supported, I thought that I could convert  
> the raster grid to a point grid.  This is an elevation grid, so  
> each point has only an elevation attribute.
>
> When I run shp2pgsql on the resulting point shapefile, it creates a  
> sql file with no problems.
>
> However, when I run the sql file in PGAdminIII, PGAdminIII  
> terminates without any errors or warnings – just shuts down.
>
> I’ve tried this several times, trying to make sure that I wasn’t  
> making a mistake – but can’t find any.
>
>
>
> Is there a file size limit either in PGAdminIII or in PostGIS/ 
> PostgreSQL that might be causing this issue?
>
>
>
> Any ideas of why the sql file would cause PGAdminIII to terminate  
> and is there an alternate means of loading this data?
>
>
>
> -Thomas
>
>
>
>
>
> Message: 6
>
> Date: Fri, 25 Jan 2008 22:06:32 -0800
>
> From: Paul Ramsey <pramsey at refractions.net>
>
> Subject: Re: [postgis-users] newbie question
>
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>
> Message-ID: <7C2F8D01-D121-419E-BE95-36BCFFE6569B at refractions.net>
>
> Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes;
>
>       format=flowed
>
>
>
> Or the more succinct and modern
>
>
>
> SELECT the_geom, zone
>
> FROM r_zones
>
> WHERE
>
>    ST_Contains(the_geom,GeomFromText('POINT(7644373.465626
>
> 687692.342075)', 2838));
>
>
>
>
>
> On 25-Jan-08, at 8:14 PM, Stephen Woodbridge wrote:
>
>
>
> > Try:
>
> >
>
> > SELECT the_geom, zone
>
> >   FROM r_zones
>
> >  WHERE
>
> >    setsrid(makepoint(7644373.465626, 687692.342075), 2838) &&  
> the_geom
>
> >    and within(setsrid(makepoint(7644373.465626, 687692.342075),  
> 2838),
>
> > the_geom);
>
> >
>
> > Now this assumes that the_geom of r_zones is also in srid 2838.  
> If not
>
> > you will need to project the point into whatever srid that  
> r_zones is
>
> > in.
>
> >
>
> > HTH,
>
> >  -Stephen Woodbridge
>
> >   http://imaptools.com/
>
> >
>
> > Thomas Crosslin wrote:
>
> >> I’m a new PostGIS/PostgreSQL user.
>
> >> I’ve successfully set up my database and uploaded a few  
> shapefiles to
>
> >> play with.
>
> >> I want to do some spatial queries and I’m having some difficulty.
>
> >>  I’d like to extract the polygon that a point intersects –  
> actually I
>
> >> really want to be able to intersect several layers at once with a
>
> >> given point and return the values from a particular field from each
>
> >> layer.  But I decided to start with one layer.  So the table is
>
> >> called “r_zones” (it is an imported shapefile) and the field I want
>
> >> information returned from is called “zone”.  So what I want is to
>
> >> know what “zone” the point falls within.  I’m using PgAdmin  
> (v1.6.2)
>
> >> to query my database, Windows, Postgres8.2, PostGIS 1.3.1.
>
> >>  So I’ve tried a basic query like this:
>
> >>  SELECT the_geom, zone
>
> >> FROM r_zones
>
> >> WHERE GeomFromText('POINT(7644373.465626 687692.342075)', 2838)  I
>
> >> know the WHERE statement is incorrect, I’ve tried several variants
>
> >> looking at the docs (like using a distance based query and others)
>
> >> but cannot make a successful query.  Where am I going wrong?
>
> >>   Any help appreciated.
>
> >>  -Thomas
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users