[GIS] How does shp2pgsql define schema

importpostgisshapefile

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?

Best Answer

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;
}
Related Question