How does shp2pgsql define schema?

Question

Can anyone explain how it is that the shp2pgsql loader for loading shapefiles into PostGIS goes about defining the schema for a table when importing a shapefile?

To further explain, shapefiles are comprised of geometry plus attributes of different data types and lengths: text, float, double, etc. When running shp2pgsql with the -c option to create a table, it defines the data types on its own somehow, but it's not clear how this happens. The only other option that looks related is -i, to use int4 for all integer fields.

Does it read into the file to find out the lengths of data values and consider those first? Does it just assume basic things like text, integer, and float with default lengths?

Answer 1

If you want to know how shp2pgsql works you can go to the source. You can download it here and the path to the code for this utility is in the postgis-1.5.1\loader\ folder.

A quick browse led me to the shp2pgsql-core.c lines 1288-1399 for the CREATE TABLE logic.

It looks like this portion (lines 1309-1360) might be what you are after:

switch (state->types[j])
{
case FTString:
    /* use DBF attribute size as maximum width */
    vasbappend(sb, "varchar(%d)", state->widths[j]);
    break;

case FTDate:
    vasbappend(sb, "date");
    break;

case FTInteger:
    /* Determine exact type based upon field width */
    if (state->config->forceint4)
    {
        vasbappend(sb, "int4");
    }
    else if (state->widths[j] < 5)
    {
        vasbappend(sb, "int2");
    }
    else if (state->widths[j] < 10)
    {
        vasbappend(sb, "int4");
    }
    else
    {
        vasbappend(sb, "numeric(%d,0)", state->widths[j]);
    }
    break;

case FTDouble:
    /* Determine exact type based upon field width */
    if (state->widths[j] > 18)
    {
        vasbappend(sb, "numeric");
    }
    else
    {
        vasbappend(sb, "float8");
    }
    break;

case FTLogical:
    vasbappend(sb, "boolean");
    break;

default:
    snprintf(state->message, SHPLOADERMSGLEN, "Invalid type %x in DBF file", state->types[j]);
    stringbuffer_destroy(sb);
    return SHPLOADERERR;
}

Answer 2

The shapefile contains a header that describes all of the columns in the DBF file and the geometry type. See http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf for a full description of ShapeFiles.

There are also any number of open source shapefile readers in addition to shp2pgsql that you can look at to see how it's done. I'd google for shapefile reader and your language of choice.