[postgis-users] shp2pgsql nulls

Andy Colson andy at squeakycode.net
Thu Sep 29 08:38:06 PDT 2011


On 9/29/2011 10:05 AM, Mark Cave-Ayland wrote:
> On 29/09/11 15:01, Andy Colson wrote:
>
>> Hey Mark, I may have guessed wrong. I got a response from the gdal folk,
>> who said "the null values for Date in this dbf file were indicated by a
>> string filled with spaces".
>>
>> Would still be neet if something could be done so shp2pgsql could import
>> this. The shapefile is 16 Meg, and uncompresses to 157 Meg, you can get
>> it here:
>>
>> http://squeakycode.net/parcelAnnoV2.zip
>>
>> Version: is this what you are looking for?
>>
>> RCSID: $Id: shp2pgsql-core.h 5983 2010-09-19 11:27:05Z mcayland $
>> RELEASE: 1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
>>
>> -Andy
>
> Hi Andy,
>
> Thanks for the example, but I don't appear to be able to recreate this
> here:
>
> pg90 at zeno:~/datasets$ shp2pgsql ParcelAnnoV2 > test.sql
> pg90 at zeno:~/datasets$ psql -d postgis15 -f test.sql
>
> postgis15=# \d parcelannov2
> Table "public.parcelannov2"
> Column | Type | Modifiers
> ------------+------------------------+------------------------------------------------------------
>
> gid | integer | not null default nextval('parcelannov2_gid_seq'::regclass)
> textstring | character varying(254) |
> fontname | character varying(254) |
> fontsize | numeric |
> bold | smallint |
> italic | smallint |
> underline | smallint |
> verticalal | smallint |
> horizontal | smallint |
> xoffset | numeric |
> yoffset | numeric |
> angle | numeric |
> fontleadin | numeric |
> wordspacin | numeric |
> characterw | numeric |
> characters | numeric |
> flipangle | numeric |
> override | integer |
> featype | smallint |
> bptype | smallint |
> book | integer |
> page | integer |
> recorded | date |
> taskid | integer |
> the_geom | geometry |
> Indexes:
> "parcelannov2_pkey" PRIMARY KEY, btree (gid)
> Check constraints:
> "enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
> "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
> 'MULTIPOLYGON'::text OR the_geom IS NULL)
> "enforce_srid_the_geom" CHECK (st_srid(the_geom) = (-1))
>
> postgis15=# select * from parcelannov2 where gid = 1;
> gid | textstring | fontname | fontsize | bold | italic | underline |
> verticalal | horizontal | xoffset | yoffset | angle | fontleadin |
> wordspacin | characterw |
> characters | flipangle | override | featype | bptype | book | page |
> recorded | taskid | the_geom
> -----+------------+----------+---------------+------+--------+-----------+------------+------------+---------------+---------------+---------------+---------------+---------------+---------------+
>
> ---------------+---------------+----------+---------+--------+------+------+----------+--------+----------
>
> 1 | | | 0.00000000000 | 0 | 0 | 0 | 0 | 0 | 0.00000000000 |
> 0.00000000000 | 0.00000000000 | 0.00000000000 | 0.00000000000 |
> 0.00000000000 |
> 0.00000000000 | 0.00000000000 | 0 | 20 | 20 | 0 | 0 | | 0 |
> (1 row)
>
> postgis15=# select * from parcelannov2 where gid = 1 and recorded is null;
> gid | textstring | fontname | fontsize | bold | italic | underline |
> verticalal | horizontal | xoffset | yoffset | angle | fontleadin |
> wordspacin | characterw |
> characters | flipangle | override | featype | bptype | book | page |
> recorded | taskid | the_geom
> -----+------------+----------+---------------+------+--------+-----------+------------+------------+---------------+---------------+---------------+---------------+---------------+---------------+
>
> ---------------+---------------+----------+---------+--------+------+------+----------+--------+----------
>
> 1 | | | 0.00000000000 | 0 | 0 | 0 | 0 | 0 | 0.00000000000 |
> 0.00000000000 | 0.00000000000 | 0.00000000000 | 0.00000000000 |
> 0.00000000000 |
> 0.00000000000 | 0.00000000000 | 0 | 20 | 20 | 0 | 0 | | 0 |
> (1 row)
>
>
> That seems to indicate that the recorded date field is set to NULL as
> intended? This is using the current 1.5 branch HEAD.
>
>
> ATB,
>
> Mark.
>

Oh, buggers.  I had problems with this shapefile and tried several 
different things, and I forgot to mention an important step.  Which 
probably takes the error out of postgis territory.

See, I want to change the projection.  So I ran ogr2ogr from 
ParcelAnnoV2 to temp_shapefile first, then shp2pgsql on temp_shapefile 
would give me invalid date errors.  Sorry.  I've got too many things 
going on and messing things up.

The gdal people said the patch was applied to the shapefile driver:

http://lists.osgeo.org/pipermail/gdal-dev/2011-September/030276.html

So I'm wondering if with that fix, the shape->shape would produce a new 
shapefile that shp2pgsql would import just fine.

I still have not had a chance to try out their fix, but at this point I 
think all is well, and I've wasted enough of your time, we can call this 
resolved.

Thanks again,

-Andy



More information about the postgis-users mailing list