[postgis-users] PostGIS/PostGres Configuration

Thu Mar 18 11:31:00 PDT 2010

Steffan,

When you say you've tried the shp2pgsql command without success, do
you mean it crashes also? Or do you have some other problem.

If you could share the civic point data with me I could attempt to
diagnose the problem you are having there.

The 2Gb file limit is partly fixable in any event: dbf files larger
than 2Gb can be supported, but shp files can only be moved up to a 4Gb
maximum. After that, the shape file format itself simple can't support
larger files (the shx index file only supports a 32-bit offset address
record).

You can use a tool like FME (safe.com/fme) to read pretty much any
input format into PostGIS. ogr2ogr can also be used, to some extent,
to stream changes from one supported data source into PostGIS.

Paul

On Thu, Mar 18, 2010 at 11:05 AM, Steffan Chmuryk
<schmuryk at icisociety.ca> wrote:
> Back again...
>
> I have attempted to run the shp2pgsql command, without success. So I decided to test some other datasets, and see what happens.
>
> Two polygon datasets, 300mb and 950mb, and one point dataset, 18mb, all loaded successfully. The civic address point dataset of 500mb, still does not load.
>
> Any suggestions?
>
> Also, when will PostGIS be able to load data that is larger than 2gb? And, is there any other intermediate format, other than shapefile, that I should consider using?
>
> Steffan Chmuryk
> Technical Services Coordinator, ICIS
> www.icisociety.ca
>
> Office:   (250) 381-9295
> Cell:     (250) 516-2481
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul Ramsey
> Sent: Tuesday, March 16, 2010 6:25 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] PostGIS/PostGres Configuration
>
> Don't need to... run this command:
>
> Shp2pgsql -s 3005 -D CIVIC_ADDRESS.shp civic_address > CIVIC_ADDRESS.sql
>
> Then run
>
> psql -d mydatabase -f CIVIC_ADDRESS.sql
>
> Hopefully something useful happens.
>
> P
>
> On Tue, Mar 16, 2010 at 5:36 PM, Steffan Chmuryk <schmuryk at icisociety.ca> wrote:
>> OK, not sure if I did this correctly. I used the psql console, under plugins in pgAdmin III. This is my command:
>>
>> Shp2pgsql -s 3005 -D CIVIC_ADDRESS.shp CIVIC_ADDRESS.dbf > CIVIC_ADDRESS.sql
>>
>> ... now this is where it gets amateurish...
>>
>> I haven't specified a directory, and I haven't created a table in pgAdmin.
>>
>> Steffan Chmuryk
>> Technical Services Coordinator, ICIS
>> www.icisociety.ca
>>
>> Office:   (250) 381-9295
>> Cell:     (250) 516-2481
>>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul Ramsey
>> Sent: Tuesday, March 16, 2010 5:39 PM
>> To: PostGIS Users Discussion
>> Subject: Re: [postgis-users] PostGIS/PostGres Configuration
>>
>> Steffan, could you try passing the smaller of the two datasets through
>> the commandline shp2pgsql tool?
>>
>> shp2pgsql -s 3005 -D thefile.shp thetable > thefile.sql
>>
>> See if it goes boom...
>>
>> P
>>
>> On Tue, Mar 16, 2010 at 5:21 PM, Steffan Chmuryk <schmuryk at icisociety.ca> wrote:
>>> Thanks,
>>>
>>> Actually, I have been working with two datasets. One of which is 470mb total, and the other is 2.27gb total; however, both of these shapefiles encounter the same issues. The 470mb data is an addressing dataset, point based, and the 2.27gb file is a cadastre dataset, polygon based.
>>>
>>> Steffan Chmuryk
>>> Technical Services Coordinator, ICIS
>>> www.icisociety.ca
>>>
>>> Office: (250) 381-9295
>>> Cell:     (250) 516-2481
>>>
>>> -----Original Message-----
>>> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul Ramsey
>>> Sent: Tuesday, March 16, 2010 5:31 PM
>>> To: PostGIS Users Discussion
>>> Subject: Re: [postgis-users] PostGIS/PostGres Configuration
>>>
>>> Steffan,
>>> Is your bigger file by any chance actually bigger than 2GB in any of
>>> its components? Large (>2GB) files are not currently supported in
>>> release, though we do have a patch on the issue in the tracker.
>>> P
>>>
>>> On Tue, Mar 16, 2010 at 5:15 PM, Steffan Chmuryk <schmuryk at icisociety.ca> wrote:
>>>> Thank you for your help Paul;
>>>>
>>>> I have successfully loaded a dataset, a 30mb administrative boundaries file. I have also viewed this information successfully. Now I am trying to load a larger, 2gb, file. Using the shp2pgsql tool, I have encountered an error:
>>>>
>>>> An unhandled win32 exception occurred in shp2pgsql-gui.exe [3996]. Just-in-Time debugging this exception failed with the following error: No installed debugger has Just-In-Time debugging enabled.
>>>>
>>>> So I installed a new debugger, the WinDbg debugger, and executed it; however, now I am not getting an error message, and my data is still not being loaded to postgis/postgre. I then modified the data execution prevention to work only for windows programs. Still haven't gotten anywhere.
>>>>
>>>> I am using the shp2pgsql tool by itself, am I missing a step?
>>>>
>>>> Steffan Chmuryk
>>>> Technical Services Coordinator, ICIS
>>>> www.icisociety.ca
>>>>
>>>> Office: (250) 381-9295
>>>> Cell:     (250) 516-2481
>>>>
>>>> -----Original Message-----
>>>> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul Ramsey
>>>> Sent: Monday, March 15, 2010 8:38 PM
>>>> To: PostGIS Users Discussion
>>>> Subject: Re: [postgis-users] PostGIS/PostGres Configuration
>>>>
>>>> From an unrelated post on udig-devel it looks like the error you are
>>>> seeing is common to the most recent 1.2M release... try backing up to
>>>> the last 1.1 release and using that for PostGIS access.
>>>>
>>>> Also, if you're doing general desktop tests, you could try gvSIG,
>>>> CadCorp SIS and (!!) MapInfo's latest release.
>>>>
>>>> P.
>>>>
>>>> On Mon, Mar 15, 2010 at 5:30 PM, Steffan Chmuryk <schmuryk at icisociety.ca> wrote:
>>>>> Thanks,
>>>>>
>>>>> So now, I have set up the connection, and have tried to connect via all three applications. Here are the errors:
>>>>>
>>>>> * Udig will connect, and seems to load the data, but then gives this message: RenderingException(s) occurred during rendering: Must call hasNext before calling next
>>>>>        *Seems to be a java error.
>>>>> * OpenJump connects to the db, but gives this message: Error: syntax error at or near "ST_GeomFromText" (PSQL Exception)
>>>>>        *Seems to require a plugin.
>>>>> * QGIS seems to work fine.
>>>>>
>>>>> More to come...
>>>>>
>>>>> Steffan Chmuryk
>>>>> Technical Services Coordinator, ICIS
>>>>> www.icisociety.ca
>>>>>
>>>>> Office: (250) 381-9295
>>>>> Cell:     (250) 516-2481
>>>>> -----Original Message-----
>>>>> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul Ramsey
>>>>> Sent: Monday, March 15, 2010 12:15 PM
>>>>> To: PostGIS Users Discussion
>>>>> Subject: Re: [postgis-users] PostGIS/PostGres Configuration
>>>>>
>>>>> Steffan,
>>>>> Leave your localhost entry in place, it's useful. Make a copy of the
>>>>> line and edit the copy.
>>>>> Change localhost to 192.168.0.0 and change 32 to 16.
>>>>> Ensure that listen_addresses = '*' in your postgresql.conf file.
>>>>> Restart the database
>>>>> Try again.
>>>>> P
>>>>>
>>>>> On Mon, Mar 15, 2010 at 11:50 AM, Steffan Chmuryk
>>>>> <schmuryk at icisociety.ca> wrote:
>>>>>> Hello,
>>>>>>
>>>>>> This is my first post, so please go easy ;)
>>>>>>
>>>>>> I have built a PostGIS/PostGres spatial database, and have successfully
>>>>>> imported a shapefile. Now when I try to connect to the database via
>>>>>> Udig, OpenJump, or Qgis, I the following error:
>>>>>>
>>>>>> Fatal: no pg_hba.conf entry for host "192.168.x.xxx", user "postgres",
>>>>>> database "postgis1', SSL off (PSQL Exception)
>>>>>>
>>>>>> Now I have tried every combination of the following:
>>>>>>
>>>>>> * Edit pg_hba.conf so that the default 127.0.0.1/32 is replaced by the
>>>>>> 192... ip
>>>>>> * Add another access configuration to the file, so that the 192... ip,
>>>>>> the postgres user, and the database are all included
>>>>>> * Enable the SSL off configuration in the postgres.conf file, and
>>>>>> restart the machine
>>>>>> * I have even explored the JDBC stuff... argh!
>>>>>>
>>>>>> The error message in the application (Udig, OpenJump, or Qgis) does not
>>>>>> change. But upon changing the files to reflect any of the above
>>>>>> configurations, I am then unable to connect to the db within pgadminIII,
>>>>>> due to an error in loading the pg_hba.conf file.
>>>>>>
>>>>>>
>>>>>> Regarding the environment:
>>>>>> * Server used to host MSSQL and SDE;
>>>>>> * Server is Windows Server 2003 (not R2);
>>>>>>
>>>>>>
>>>>>> Steffan Chmuryk
>>>>>> Technical Services Coordinator, ICIS
>>>>>> www.icisociety.ca
>>>>>>
>>>>>> Office:   (250) 381-9295
>>>>>> Cell:     (250) 516-2481
>>>>>> _______________________________________________
>>>>>> 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
>>>>>
>>>>> No virus found in this incoming message.
>>>>> Checked by AVG - www.avg.com
>>>>> Version: 9.0.733 / Virus Database: 271.1.1/2748 - Release Date: 03/15/10 00:33:00
>>>>> _______________________________________________
>>>>> 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
>>>>
>>>> No virus found in this incoming message.
>>>> Checked by AVG - www.avg.com
>>>> Version: 9.0.733 / Virus Database: 271.1.1/2748 - Release Date: 03/15/10 12:33:00
>>>> _______________________________________________
>>>> 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
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG - www.avg.com
>>> Version: 9.0.733 / Virus Database: 271.1.1/2748 - Release Date: 03/15/10 12:33:00
>>> _______________________________________________
>>> 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
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 9.0.733 / Virus Database: 271.1.1/2748 - Release Date: 03/15/10 12:33:00
>> _______________________________________________
>> 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
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.733 / Virus Database: 271.1.1/2748 - Release Date: 03/15/10 12:33:00
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>