예제 #1
0
OGRErr OGRCARTODBTableLayer::CreateField( OGRFieldDefn *poFieldIn,
                                          CPL_UNUSED int bApproxOK )
{
    GetLayerDefn();

    if (!poDS->IsReadWrite())
    {
        CPLError(CE_Failure, CPLE_AppDefined,
                 "Operation not available in read-only mode");
        return OGRERR_FAILURE;
    }

    OGRFieldDefn oField(poFieldIn);
    if( bLaunderColumnNames )
    {
        char* pszName = OGRPGCommonLaunderName(oField.GetNameRef());
        oField.SetName(pszName);
        CPLFree(pszName);
    }

/* -------------------------------------------------------------------- */
/*      Create the new field.                                           */
/* -------------------------------------------------------------------- */

    if( !bDeferedCreation )
    {
        CPLString osSQL;
        osSQL.Printf( "ALTER TABLE %s ADD COLUMN %s %s",
                    OGRCARTODBEscapeIdentifier(osName).c_str(),
                    OGRCARTODBEscapeIdentifier(oField.GetNameRef()).c_str(),
                    OGRPGCommonLayerGetType(oField, FALSE, TRUE).c_str() );
        if( !oField.IsNullable() )
            osSQL += " NOT NULL";
        if( oField.GetDefault() != NULL && !oField.IsDefaultDriverSpecific() )
        {
            osSQL += " DEFAULT ";
            osSQL += OGRPGCommonLayerGetPGDefault(&oField);
        }

        json_object* poObj = poDS->RunSQL(osSQL);
        if( poObj == NULL )
            return OGRERR_FAILURE;
        json_object_put(poObj);
    }

    poFeatureDefn->AddFieldDefn( &oField );

    return OGRERR_NONE;
}
예제 #2
0
OGRFeature* OGRCARTODBTableLayer::GetFeature( GIntBig nFeatureId )
{

    if( bDeferedCreation && RunDeferedCreationIfNecessary() != OGRERR_NONE )
        return NULL;
    FlushDeferedInsert();

    GetLayerDefn();
    
    if( osFIDColName.size() == 0 )
        return OGRCARTODBLayer::GetFeature(nFeatureId);

    CPLString osSQL = osSELECTWithoutWHERE;
    osSQL += " WHERE ";
    osSQL += OGRCARTODBEscapeIdentifier(osFIDColName).c_str();
    osSQL += " = ";
    osSQL += CPLSPrintf(CPL_FRMT_GIB, nFeatureId);

    json_object* poObj = poDS->RunSQL(osSQL);
    json_object* poRowObj = OGRCARTODBGetSingleRow(poObj);
    if( poRowObj == NULL )
    {
        if( poObj != NULL )
            json_object_put(poObj);
        return OGRCARTODBLayer::GetFeature(nFeatureId);
    }

    OGRFeature* poFeature = BuildFeature(poRowObj);
    json_object_put(poObj);

    return poFeature;
}
예제 #3
0
void OGRCARTODBTableLayer::SetDeferedCreation (OGRwkbGeometryType eGType,
                                               OGRSpatialReference* poSRS,
                                               int bGeomNullable,
                                               int bCartoDBify)
{
    bDeferedCreation = TRUE;
    nNextFID = 1;
    CPLAssert(poFeatureDefn == NULL);
    this->bCartoDBify = bCartoDBify;
    poFeatureDefn = new OGRFeatureDefn(osName);
    poFeatureDefn->Reference();
    poFeatureDefn->SetGeomType(wkbNone);
    if( eGType == wkbPolygon )
        eGType = wkbMultiPolygon;
    else if( eGType == wkbPolygon25D )
        eGType = wkbMultiPolygon25D;
    if( eGType != wkbNone )
    {
        OGRCartoDBGeomFieldDefn *poFieldDefn =
            new OGRCartoDBGeomFieldDefn("the_geom", eGType);
        poFieldDefn->SetNullable(bGeomNullable);
        poFeatureDefn->AddGeomFieldDefn(poFieldDefn, FALSE);
        if( poSRS != NULL )
        {
            poFieldDefn->nSRID = poDS->FetchSRSId( poSRS );
            poFeatureDefn->GetGeomFieldDefn(
                poFeatureDefn->GetGeomFieldCount() - 1)->SetSpatialRef(poSRS);
        }
    }
    osFIDColName = "cartodb_id";
    osBaseSQL.Printf("SELECT * FROM %s",
                     OGRCARTODBEscapeIdentifier(osName).c_str());
}
예제 #4
0
CPLString OGRCARTODBResultLayer::GetSRS_SQL(const char* pszGeomCol)
{
    CPLString osSQL;
    CPLString osLimitedSQL;

    size_t nPos = osBaseSQL.ifind(" LIMIT ");
    if( nPos != std::string::npos )
    {
        osLimitedSQL = osBaseSQL;
        size_t nSize = osLimitedSQL.size();
        for(size_t i = nPos + strlen(" LIMIT "); i < nSize; i++)
        {
            if( osLimitedSQL[i] == ' ' && osLimitedSQL[i-1] == '0')
            {
                osLimitedSQL[i-1] = '1';
                break;
            }
            osLimitedSQL[i] = '0';
        }
    }
    else
        osLimitedSQL.Printf("%s LIMIT 1", osBaseSQL.c_str());

    /* Assuming that the SRID of the first non-NULL geometry applies */
    /* to geometries of all rows. */
    osSQL.Printf("SELECT srid, srtext FROM spatial_ref_sys WHERE srid IN "
                "(SELECT ST_SRID(%s) FROM (%s) ogr_subselect)",
                OGRCARTODBEscapeIdentifier(pszGeomCol).c_str(),
                osLimitedSQL.c_str());

    return osSQL;
}
예제 #5
0
OGRErr OGRCARTODBTableLayer::DeleteFeature( GIntBig nFID )

{

    if( bDeferedCreation && RunDeferedCreationIfNecessary() != OGRERR_NONE )
        return OGRERR_FAILURE;
    FlushDeferedInsert();

    GetLayerDefn();

    if (!poDS->IsReadWrite())
    {
        CPLError(CE_Failure, CPLE_AppDefined,
                 "Operation not available in read-only mode");
        return OGRERR_FAILURE;
    }
    
    if( osFIDColName.size() == 0 )
        return OGRERR_FAILURE;
    
    CPLString osSQL;
    osSQL.Printf("DELETE FROM %s WHERE %s = " CPL_FRMT_GIB,
                    OGRCARTODBEscapeIdentifier(osName).c_str(),
                    OGRCARTODBEscapeIdentifier(osFIDColName).c_str(),
                    nFID);

    OGRErr eRet = OGRERR_FAILURE;
    json_object* poObj = poDS->RunSQL(osSQL);
    if( poObj != NULL )
    {
        json_object* poTotalRows = json_object_object_get(poObj, "total_rows");
        if( poTotalRows != NULL && json_object_get_type(poTotalRows) == json_type_int )
        {
            int nTotalRows = json_object_get_int(poTotalRows);
            if( nTotalRows > 0 )
            {
                eRet = OGRERR_NONE;
            }
            else
                eRet = OGRERR_NON_EXISTING_FEATURE;
        }
        json_object_put(poObj);
    }

    return eRet;
}
예제 #6
0
json_object* OGRCARTODBTableLayer::FetchNewFeatures(GIntBig iNext)
{
    if( osFIDColName.size() > 0 )
    {
        CPLString osSQL;
        osSQL.Printf("%s WHERE %s%s >= " CPL_FRMT_GIB " ORDER BY %s ASC LIMIT %d",
                     osSELECTWithoutWHERE.c_str(),
                     ( osWHERE.size() ) ? CPLSPrintf("%s AND ", osWHERE.c_str()) : "",
                     OGRCARTODBEscapeIdentifier(osFIDColName).c_str(),
                     iNext,
                     OGRCARTODBEscapeIdentifier(osFIDColName).c_str(),
                     GetFeaturesToFetch());
        return poDS->RunSQL(osSQL);
    }
    else
        return OGRCARTODBLayer::FetchNewFeatures(iNext);
}
예제 #7
0
OGRErr OGRCARTODBDataSource::DeleteLayer(int iLayer)
{
    if (!bReadWrite)
    {
        CPLError(CE_Failure, CPLE_AppDefined,
                 "Operation not available in read-only mode");
        return OGRERR_FAILURE;
    }

    if( iLayer < 0 || iLayer >= nLayers )
    {
        CPLError( CE_Failure, CPLE_AppDefined,
                  "Layer %d not in legal range of 0 to %d.",
                  iLayer, nLayers-1 );
        return OGRERR_FAILURE;
    }

/* -------------------------------------------------------------------- */
/*      Blow away our OGR structures related to the layer.  This is     */
/*      pretty dangerous if anything has a reference to this layer!     */
/* -------------------------------------------------------------------- */
    CPLString osLayerName = papoLayers[iLayer]->GetLayerDefn()->GetName();

    CPLDebug( "CARTODB", "DeleteLayer(%s)", osLayerName.c_str() );

    int bDeferedCreation = papoLayers[iLayer]->GetDeferedCreation();
    papoLayers[iLayer]->CancelDeferedCreation();
    delete papoLayers[iLayer];
    memmove( papoLayers + iLayer, papoLayers + iLayer + 1,
             sizeof(void *) * (nLayers - iLayer - 1) );
    nLayers--;

    if (osLayerName.size() == 0)
        return OGRERR_NONE;

    if( !bDeferedCreation )
    {
        CPLString osSQL;
        osSQL.Printf("DROP TABLE %s",
                    OGRCARTODBEscapeIdentifier(osLayerName).c_str());

        json_object* poObj = RunSQL(osSQL);
        if( poObj == NULL )
            return OGRERR_FAILURE;
        json_object_put(poObj);
    }

    return OGRERR_NONE;
}
예제 #8
0
void OGRCARTODBTableLayer::BuildWhere()

{
    osWHERE = "";

    if( m_poFilterGeom != NULL &&
        m_iGeomFieldFilter >= 0 &&
        m_iGeomFieldFilter < poFeatureDefn->GetGeomFieldCount() )
    {
        OGREnvelope  sEnvelope;

        m_poFilterGeom->getEnvelope( &sEnvelope );

        CPLString osGeomColumn(poFeatureDefn->GetGeomFieldDefn(m_iGeomFieldFilter)->GetNameRef());

        char szBox3D_1[128];
        char szBox3D_2[128];
        char* pszComma;

        CPLsnprintf(szBox3D_1, sizeof(szBox3D_1), "%.18g %.18g", sEnvelope.MinX, sEnvelope.MinY);
        while((pszComma = strchr(szBox3D_1, ',')) != NULL)
            *pszComma = '.';
        CPLsnprintf(szBox3D_2, sizeof(szBox3D_2), "%.18g %.18g", sEnvelope.MaxX, sEnvelope.MaxY);
        while((pszComma = strchr(szBox3D_2, ',')) != NULL)
            *pszComma = '.';
        osWHERE.Printf("(%s && 'BOX3D(%s, %s)'::box3d)",
                       OGRCARTODBEscapeIdentifier(osGeomColumn).c_str(),
                       szBox3D_1, szBox3D_2 );
    }

    if( strlen(osQuery) > 0 )
    {
        if( osWHERE.size() > 0 )
            osWHERE += " AND ";
        osWHERE += osQuery;
    }

    if( osFIDColName.size() == 0 )
    {
        osBaseSQL = osSELECTWithoutWHERE;
        if( osWHERE.size() )
        {
            osBaseSQL += " WHERE ";
            osBaseSQL += osWHERE;
        }
    }
}
예제 #9
0
GIntBig OGRCARTODBTableLayer::GetFeatureCount(int bForce)
{

    if( bDeferedCreation && RunDeferedCreationIfNecessary() != OGRERR_NONE )
        return 0;
    FlushDeferedInsert();

    GetLayerDefn();

    CPLString osSQL(CPLSPrintf("SELECT COUNT(*) FROM %s",
                               OGRCARTODBEscapeIdentifier(osName).c_str()));
    if( osWHERE.size() )
    {
        osSQL += " WHERE ";
        osSQL += osWHERE;
    }

    json_object* poObj = poDS->RunSQL(osSQL);
    json_object* poRowObj = OGRCARTODBGetSingleRow(poObj);
    if( poRowObj == NULL )
    {
        if( poObj != NULL )
            json_object_put(poObj);
        return OGRCARTODBLayer::GetFeatureCount(bForce);
    }

    json_object* poCount = json_object_object_get(poRowObj, "count");
    if( poCount == NULL || json_object_get_type(poCount) != json_type_int )
    {
        json_object_put(poObj);
        return OGRCARTODBLayer::GetFeatureCount(bForce);
    }

    GIntBig nRet = (GIntBig)json_object_get_int64(poCount);

    json_object_put(poObj);

    return nRet;
}
예제 #10
0
OGRLayer   *OGRCARTODBDataSource::ICreateLayer( const char *pszName,
                                           OGRSpatialReference *poSpatialRef,
                                           OGRwkbGeometryType eGType,
                                           char ** papszOptions )
{
    if (!bReadWrite)
    {
        CPLError(CE_Failure, CPLE_AppDefined, "Operation not available in read-only mode");
        return NULL;
    }

/* -------------------------------------------------------------------- */
/*      Do we already have this layer?  If so, should we blow it        */
/*      away?                                                           */
/* -------------------------------------------------------------------- */
    int iLayer;

    for( iLayer = 0; iLayer < nLayers; iLayer++ )
    {
        if( EQUAL(pszName,papoLayers[iLayer]->GetName()) )
        {
            if( CSLFetchNameValue( papszOptions, "OVERWRITE" ) != NULL
                && !EQUAL(CSLFetchNameValue(papszOptions,"OVERWRITE"),"NO") )
            {
                DeleteLayer( iLayer );
            }
            else
            {
                CPLError( CE_Failure, CPLE_AppDefined,
                          "Layer %s already exists, CreateLayer failed.\n"
                          "Use the layer creation option OVERWRITE=YES to "
                          "replace it.",
                          pszName );
                return NULL;
            }
        }
    }

    int nSRID = 0;
    if( poSpatialRef != NULL )
        nSRID = FetchSRSId( poSpatialRef );

    CPLString osGeomType;
    if( eGType != wkbNone )
    {
        osGeomType = OGRToOGCGeomType(eGType);
        if( eGType & wkb25DBit )
            osGeomType += "Z";
    }

    CPLString osSQL;
    osSQL.Printf("CREATE TABLE %s ( %s SERIAL, ",
                 OGRCARTODBEscapeIdentifier(pszName).c_str(),
                 "cartodb_id");
    if( osGeomType.size() > 0 )
    {
        osSQL += CPLSPrintf("%s GEOMETRY(%s, %d), %s GEOMETRY(%s, %d),",
                 "the_geom",
                 osGeomType.c_str(),
                 nSRID,
                 "the_geom_webmercator",
                 osGeomType.c_str(),
                 3857);
    }
    osSQL += CPLSPrintf("PRIMARY KEY (%s) )", "cartodb_id");

    osSQL += ";";
    osSQL += CPLSPrintf("DROP SEQUENCE IF EXISTS %s",
                        OGRCARTODBEscapeIdentifier(CPLSPrintf("%s_%s_seq", pszName, "cartodb_id")).c_str());
    osSQL += ";";
    osSQL += CPLSPrintf("CREATE SEQUENCE %s START 1",
                        OGRCARTODBEscapeIdentifier(CPLSPrintf("%s_%s_seq", pszName, "cartodb_id")).c_str());
    osSQL += ";";
    osSQL += CPLSPrintf("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT nextval('%s')",
                        pszName,
                        "cartodb_id",
                        OGRCARTODBEscapeLiteral(CPLSPrintf("%s_%s_seq", pszName, "cartodb_id")).c_str());

    json_object* poObj = RunSQL(osSQL);
    if( poObj == NULL )
        return NULL;
    json_object_put(poObj);

    OGRCARTODBTableLayer* poLayer = new OGRCARTODBTableLayer(this, pszName);
    papoLayers = (OGRLayer**) CPLRealloc(
                    papoLayers, (nLayers + 1) * sizeof(OGRLayer*));
    papoLayers[nLayers ++] = poLayer;

    return poLayer;
}
예제 #11
0
OGRErr OGRCARTODBTableLayer::ISetFeature( OGRFeature *poFeature )

{
    int i;

    if( bDeferedCreation && RunDeferedCreationIfNecessary() != OGRERR_NONE )
        return OGRERR_FAILURE;
    FlushDeferedInsert();

    GetLayerDefn();

    if (!poDS->IsReadWrite())
    {
        CPLError(CE_Failure, CPLE_AppDefined,
                 "Operation not available in read-only mode");
        return OGRERR_FAILURE;
    }

    if (poFeature->GetFID() == OGRNullFID)
    {
        CPLError( CE_Failure, CPLE_AppDefined,
                  "FID required on features given to SetFeature()." );
        return OGRERR_FAILURE;
    }
    
    CPLString osSQL;
    osSQL.Printf("UPDATE %s SET ", OGRCARTODBEscapeIdentifier(osName).c_str());
    int bMustComma = FALSE;
    for(i = 0; i < poFeatureDefn->GetFieldCount(); i++)
    {
        if( bMustComma )
            osSQL += ", ";
        else
            bMustComma = TRUE;

        osSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetFieldDefn(i)->GetNameRef());
        osSQL += " = ";

        if( !poFeature->IsFieldSet(i) )
        {
            osSQL += "NULL";
        }
        else
        {
            OGRFieldType eType = poFeatureDefn->GetFieldDefn(i)->GetType();
            if( eType == OFTString || eType == OFTDateTime || eType == OFTDate || eType == OFTTime )
            {
                osSQL += "'";
                osSQL += OGRCARTODBEscapeLiteral(poFeature->GetFieldAsString(i));
                osSQL += "'";
            }
            else if( (eType == OFTInteger || eType == OFTInteger64) &&
                poFeatureDefn->GetFieldDefn(i)->GetSubType() == OFSTBoolean )
            {
                osSQL += poFeature->GetFieldAsInteger(i) ? "'t'" : "'f'";
            }
            else
                osSQL += poFeature->GetFieldAsString(i);
        }
    }

    for(i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++)
    {
        if( bMustComma )
            osSQL += ", ";
        else
            bMustComma = TRUE;

        osSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetGeomFieldDefn(i)->GetNameRef());
        osSQL += " = ";

        OGRGeometry* poGeom = poFeature->GetGeomFieldRef(i);
        if( poGeom == NULL )
        {
            osSQL += "NULL";
        }
        else
        {
            OGRCartoDBGeomFieldDefn* poGeomFieldDefn =
                (OGRCartoDBGeomFieldDefn *)poFeatureDefn->GetGeomFieldDefn(i);
            int nSRID = poGeomFieldDefn->nSRID;
            if( nSRID == 0 )
                nSRID = 4326;
            char* pszEWKB = OGRGeometryToHexEWKB(poGeom, nSRID, FALSE);
            osSQL += "'";
            osSQL += pszEWKB;
            osSQL += "'";
            CPLFree(pszEWKB);
        }
    }

    osSQL += CPLSPrintf(" WHERE %s = " CPL_FRMT_GIB,
                    OGRCARTODBEscapeIdentifier(osFIDColName).c_str(),
                    poFeature->GetFID());

    OGRErr eRet = OGRERR_FAILURE;
    json_object* poObj = poDS->RunSQL(osSQL);
    if( poObj != NULL )
    {
        json_object* poTotalRows = json_object_object_get(poObj, "total_rows");
        if( poTotalRows != NULL && json_object_get_type(poTotalRows) == json_type_int )
        {
            int nTotalRows = json_object_get_int(poTotalRows);
            if( nTotalRows > 0 )
            {
                eRet = OGRERR_NONE;
            }
            else
                eRet = OGRERR_NON_EXISTING_FEATURE;
        }
        json_object_put(poObj);
    }

    return eRet;
}
예제 #12
0
OGRErr OGRCARTODBTableLayer::ICreateFeature( OGRFeature *poFeature )

{
    int i;

    if( bDeferedCreation )
    {
        if( RunDeferedCreationIfNecessary() != OGRERR_NONE )
            return OGRERR_FAILURE;
    }

    GetLayerDefn();
    int bHasUserFieldMatchingFID = FALSE;
    if( osFIDColName.size() )
        bHasUserFieldMatchingFID = poFeatureDefn->GetFieldIndex(osFIDColName) >= 0;

    if (!poDS->IsReadWrite())
    {
        CPLError(CE_Failure, CPLE_AppDefined,
                 "Operation not available in read-only mode");
        return OGRERR_FAILURE;
    }

    CPLString osSQL;

    int bHasJustGotNextFID = FALSE;
    if( !bHasUserFieldMatchingFID && bInDeferedInsert && nNextFID < 0 && osFIDColName.size() )
    {
        osSQL.Printf("SELECT nextval('%s') AS nextid",
                     OGRCARTODBEscapeLiteral(CPLSPrintf("%s_%s_seq", osName.c_str(), osFIDColName.c_str())).c_str());

        json_object* poObj = poDS->RunSQL(osSQL);
        json_object* poRowObj = OGRCARTODBGetSingleRow(poObj);
        if( poRowObj != NULL )
        {
            json_object* poID = json_object_object_get(poRowObj, "nextid");
            if( poID != NULL && json_object_get_type(poID) == json_type_int )
            {
                nNextFID = json_object_get_int64(poID);
                bHasJustGotNextFID = TRUE;
            }
        }

        if( poObj != NULL )
            json_object_put(poObj);
    }

    osSQL.Printf("INSERT INTO %s ", OGRCARTODBEscapeIdentifier(osName).c_str());
    int bMustComma = FALSE;
    for(i = 0; i < poFeatureDefn->GetFieldCount(); i++)
    {
        if( !poFeature->IsFieldSet(i) )
            continue;

        if( bMustComma )
            osSQL += ", ";
        else
        {
            osSQL += "(";
            bMustComma = TRUE;
        }

        osSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetFieldDefn(i)->GetNameRef());
    }

    for(i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++)
    {
        if( poFeature->GetGeomFieldRef(i) == NULL )
            continue;

        if( bMustComma )
            osSQL += ", ";
        else
        {
            osSQL += "(";
            bMustComma = TRUE;
        }

        osSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetGeomFieldDefn(i)->GetNameRef());
    }
    
    if( !bHasUserFieldMatchingFID &&
        osFIDColName.size() && (poFeature->GetFID() != OGRNullFID || nNextFID >= 0) )
    {
        if( bMustComma )
            osSQL += ", ";
        else
        {
            osSQL += "(";
            bMustComma = TRUE;
        }

        osSQL += OGRCARTODBEscapeIdentifier(osFIDColName);
    }

    if( !bMustComma )
        osSQL += " DEFAULT VALUES";
    else
    {
        osSQL += ") VALUES (";
        
        bMustComma = FALSE;
        for(i = 0; i < poFeatureDefn->GetFieldCount(); i++)
        {
            if( !poFeature->IsFieldSet(i) )
                continue;
        
            if( bMustComma )
                osSQL += ", ";
            else
                bMustComma = TRUE;
            
            OGRFieldType eType = poFeatureDefn->GetFieldDefn(i)->GetType();
            if( eType == OFTString || eType == OFTDateTime || eType == OFTDate || eType == OFTTime )
            {
                osSQL += "'";
                osSQL += OGRCARTODBEscapeLiteral(poFeature->GetFieldAsString(i));
                osSQL += "'";
            }
            else if( (eType == OFTInteger || eType == OFTInteger64) &&
                     poFeatureDefn->GetFieldDefn(i)->GetSubType() == OFSTBoolean )
            {
                osSQL += poFeature->GetFieldAsInteger(i) ? "'t'" : "'f'";
            }
            else
                osSQL += poFeature->GetFieldAsString(i);
        }
        
        for(i = 0; i < poFeatureDefn->GetGeomFieldCount(); i++)
        {
            OGRGeometry* poGeom = poFeature->GetGeomFieldRef(i);
            if( poGeom == NULL )
                continue;
        
            if( bMustComma )
                osSQL += ", ";
            else
                bMustComma = TRUE;

            OGRCartoDBGeomFieldDefn* poGeomFieldDefn =
                (OGRCartoDBGeomFieldDefn *)poFeatureDefn->GetGeomFieldDefn(i);
            int nSRID = poGeomFieldDefn->nSRID;
            if( nSRID == 0 )
                nSRID = 4326;
            char* pszEWKB;
            if( wkbFlatten(poGeom->getGeometryType()) == wkbPolygon &&
                wkbFlatten(GetGeomType()) == wkbMultiPolygon )
            {
                OGRMultiPolygon* poNewGeom = new OGRMultiPolygon();
                poNewGeom->addGeometry(poGeom);
                pszEWKB = OGRGeometryToHexEWKB(poNewGeom, nSRID, FALSE);
                delete poNewGeom;
            }
            else
                pszEWKB = OGRGeometryToHexEWKB(poGeom, nSRID, FALSE);
            osSQL += "'";
            osSQL += pszEWKB;
            osSQL += "'";
            CPLFree(pszEWKB);
        }

        if( !bHasUserFieldMatchingFID )
        {
            if( osFIDColName.size() && nNextFID >= 0 )
            {
                if( bMustComma )
                    osSQL += ", ";
                else
                    bMustComma = TRUE;

                if( bHasJustGotNextFID )
                {
                    osSQL += CPLSPrintf(CPL_FRMT_GIB, nNextFID);
                }
                else
                {
                    osSQL += CPLSPrintf("nextval('%s')",
                            OGRCARTODBEscapeLiteral(CPLSPrintf("%s_%s_seq", osName.c_str(), osFIDColName.c_str())).c_str());
                }
                poFeature->SetFID(nNextFID);
                nNextFID ++;
            }
            else if( osFIDColName.size() && poFeature->GetFID() != OGRNullFID )
            {
                if( bMustComma )
                    osSQL += ", ";
                else
                    bMustComma = TRUE;

                osSQL += CPLSPrintf(CPL_FRMT_GIB, poFeature->GetFID());
            }
        }

        osSQL += ")";
    }

    if( bInDeferedInsert )
    {
        OGRErr eRet = OGRERR_NONE;
        if( osDeferedInsertSQL.size() != 0 &&
            (int)osDeferedInsertSQL.size() + (int)osSQL.size() > nMaxChunkSize )
        {
            osDeferedInsertSQL = "BEGIN;" + osDeferedInsertSQL + "COMMIT;";
            json_object* poObj = poDS->RunSQL(osDeferedInsertSQL);
            if( poObj != NULL )
                json_object_put(poObj);
            else
            {
                bInDeferedInsert = FALSE;
                eRet = OGRERR_FAILURE;
            }
            osDeferedInsertSQL = "";
        }

        osDeferedInsertSQL += osSQL;
        osDeferedInsertSQL += ";";

        if( (int)osDeferedInsertSQL.size() > nMaxChunkSize )
        {
            osDeferedInsertSQL = "BEGIN;" + osDeferedInsertSQL + "COMMIT;";
            json_object* poObj = poDS->RunSQL(osDeferedInsertSQL);
            if( poObj != NULL )
                json_object_put(poObj);
            else
            {
                bInDeferedInsert = FALSE;
                eRet = OGRERR_FAILURE;
            }
            osDeferedInsertSQL = "";
        }

        return eRet;
    }
    
    if( osFIDColName.size() )
    {
        osSQL += " RETURNING ";
        osSQL += OGRCARTODBEscapeIdentifier(osFIDColName);

        json_object* poObj = poDS->RunSQL(osSQL);
        json_object* poRowObj = OGRCARTODBGetSingleRow(poObj);
        if( poRowObj == NULL )
        {
            if( poObj != NULL )
                json_object_put(poObj);
            return OGRERR_FAILURE;
        }

        json_object* poID = json_object_object_get(poRowObj, osFIDColName);
        if( poID != NULL && json_object_get_type(poID) == json_type_int )
        {
            poFeature->SetFID(json_object_get_int64(poID));
        }

        if( poObj != NULL )
            json_object_put(poObj);

        return OGRERR_NONE;
    }
    else
    {
        OGRErr eRet = OGRERR_FAILURE;
        json_object* poObj = poDS->RunSQL(osSQL);
        if( poObj != NULL )
        {
            json_object* poTotalRows = json_object_object_get(poObj, "total_rows");
            if( poTotalRows != NULL && json_object_get_type(poTotalRows) == json_type_int )
            {
                int nTotalRows = json_object_get_int(poTotalRows);
                if( nTotalRows == 1 )
                {
                    eRet = OGRERR_NONE;
                }
            }
            json_object_put(poObj);
        }

        return eRet;
    }
}
예제 #13
0
OGRErr OGRCARTODBTableLayer::RunDeferedCreationIfNecessary()
{
    if( !bDeferedCreation )
        return OGRERR_NONE;
    bDeferedCreation = FALSE;

    CPLString osSQL;
    osSQL.Printf("CREATE TABLE %s ( %s SERIAL,",
                 OGRCARTODBEscapeIdentifier(osName).c_str(),
                 osFIDColName.c_str());

    int nSRID = 0;
    OGRwkbGeometryType eGType = GetGeomType();
    if( eGType != wkbNone )
    {
        CPLString osGeomType = OGRToOGCGeomType(eGType);
        if( wkbHasZ(eGType) )
            osGeomType += "Z";

        OGRCartoDBGeomFieldDefn *poFieldDefn =
            (OGRCartoDBGeomFieldDefn *)poFeatureDefn->GetGeomFieldDefn(0);
        nSRID = poFieldDefn->nSRID;

        osSQL += CPLSPrintf("%s GEOMETRY(%s, %d)%s, %s GEOMETRY(%s, %d),",
                 "the_geom",
                 osGeomType.c_str(),
                 nSRID,
                 (!poFieldDefn->IsNullable()) ? " NOT NULL" : "",
                 "the_geom_webmercator",
                 osGeomType.c_str(),
                 3857);
    }

    for( int i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
    {
        OGRFieldDefn* poFieldDefn = poFeatureDefn->GetFieldDefn(i);
        if( strcmp(poFieldDefn->GetNameRef(), osFIDColName) != 0 )
        {
            osSQL += OGRCARTODBEscapeIdentifier(poFieldDefn->GetNameRef());
            osSQL += " ";
            osSQL += OGRPGCommonLayerGetType(*poFieldDefn, FALSE, TRUE);
            if( !poFieldDefn->IsNullable() )
                osSQL += " NOT NULL";
            if( poFieldDefn->GetDefault() != NULL && !poFieldDefn->IsDefaultDriverSpecific() )
            {
                osSQL += " DEFAULT ";
                osSQL += poFieldDefn->GetDefault();
            }
            osSQL += ",";
        }
    }

    osSQL += CPLSPrintf("PRIMARY KEY (%s) )", osFIDColName.c_str());
    
    CPLString osSeqName(OGRCARTODBEscapeIdentifier(CPLSPrintf("%s_%s_seq",
                                osName.c_str(), osFIDColName.c_str())));

    osSQL += ";";
    osSQL += CPLSPrintf("DROP SEQUENCE IF EXISTS %s CASCADE", osSeqName.c_str());
    osSQL += ";";
    osSQL += CPLSPrintf("CREATE SEQUENCE %s START 1", osSeqName.c_str());
    osSQL += ";";
    osSQL += CPLSPrintf("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT nextval('%s')",
                        OGRCARTODBEscapeIdentifier(osName).c_str(),
                        osFIDColName.c_str(), osSeqName.c_str());

    json_object* poObj = poDS->RunSQL(osSQL);
    if( poObj == NULL )
        return OGRERR_FAILURE;
    json_object_put(poObj);

    if( bCartoDBify )
    {
        if( nSRID != 4326 )
        {
            if( eGType != wkbNone )
            {
                CPLError(CE_Warning, CPLE_AppDefined,
                        "Cannot register table in dashboard with "
                        "cdb_cartodbfytable() since its SRS is not EPSG:4326");
            }
        }
        else
        {
            if( poDS->GetCurrentSchema() == "public" )
                osSQL.Printf("SELECT cdb_cartodbfytable('%s')",
                                    OGRCARTODBEscapeLiteral(osName).c_str());
            else
                osSQL.Printf("SELECT cdb_cartodbfytable('%s', '%s')",
                                    OGRCARTODBEscapeLiteral(poDS->GetCurrentSchema()).c_str(),
                                    OGRCARTODBEscapeLiteral(osName).c_str());

            poObj = poDS->RunSQL(osSQL);
            if( poObj == NULL )
                return OGRERR_FAILURE;
            json_object_put(poObj);
        }
    }

    return OGRERR_NONE;
}
예제 #14
0
OGRFeatureDefn * OGRCARTODBTableLayer::GetLayerDefnInternal(CPL_UNUSED json_object* poObjIn)
{
    if( poFeatureDefn != NULL )
        return poFeatureDefn;

    CPLString osCommand;
    if( poDS->IsAuthenticatedConnection() )
    {
        // Get everything !
        osCommand.Printf(
                 "SELECT a.attname, t.typname, a.attlen, "
                        "format_type(a.atttypid,a.atttypmod), "
                        "a.attnum, "
                        "a.attnotnull, "
                        "i.indisprimary, "
                        "pg_get_expr(def.adbin, c.oid) AS defaultexpr, "
                        "postgis_typmod_dims(a.atttypmod) dim, "
                        "postgis_typmod_srid(a.atttypmod) srid, "
                        "postgis_typmod_type(a.atttypmod)::text geomtyp, "
                        "srtext "
                 "FROM pg_class c "
                 "JOIN pg_attribute a ON a.attnum > 0 AND "
                                        "a.attrelid = c.oid AND c.relname = '%s' "
                 "JOIN pg_type t ON a.atttypid = t.oid "
                 "JOIN pg_namespace n ON c.relnamespace=n.oid AND n.nspname= '%s' "
                 "LEFT JOIN pg_index i ON c.oid = i.indrelid AND "
                                         "i.indisprimary = 't' AND a.attnum = ANY(i.indkey) "
                 "LEFT JOIN pg_attrdef def ON def.adrelid = c.oid AND "
                                              "def.adnum = a.attnum "
                 "LEFT JOIN spatial_ref_sys srs ON srs.srid = postgis_typmod_srid(a.atttypmod) "
                 "ORDER BY a.attnum",
                 OGRCARTODBEscapeLiteral(osName).c_str(),
                 OGRCARTODBEscapeLiteral(poDS->GetCurrentSchema()).c_str());
    }
    else if( poDS->HasOGRMetadataFunction() != FALSE )
    {
        osCommand.Printf( "SELECT * FROM ogr_table_metadata('%s', '%s')",
                          OGRCARTODBEscapeLiteral(poDS->GetCurrentSchema()).c_str(),
                          OGRCARTODBEscapeLiteral(osName).c_str() );
    }

    if( osCommand.size() )
    {
        if( !poDS->IsAuthenticatedConnection() && poDS->HasOGRMetadataFunction() < 0 )
            CPLPushErrorHandler(CPLQuietErrorHandler);
        OGRLayer* poLyr = poDS->ExecuteSQLInternal(osCommand);
        if( !poDS->IsAuthenticatedConnection() && poDS->HasOGRMetadataFunction() < 0 )
        {
            CPLPopErrorHandler();
            if( poLyr == NULL )
            {
                CPLDebug("CARTODB", "ogr_table_metadata(text, text) not available");
                CPLErrorReset();
            }
            else if( poLyr->GetLayerDefn()->GetFieldCount() != 12 )
            {
                CPLDebug("CARTODB", "ogr_table_metadata(text, text) has unexpected column count");
                poDS->ReleaseResultSet(poLyr);
                poLyr = NULL;
            }
            poDS->SetOGRMetadataFunction(poLyr != NULL);
        }
        if( poLyr )
        {
            OGRFeature* poFeat;
            while( (poFeat = poLyr->GetNextFeature()) != NULL )
            {
                if( poFeatureDefn == NULL )
                {
                    // We could do that outside of the while() loop, but
                    // by doing that here, we are somewhat robust to
                    // ogr_table_metadata() returning suddenly an empty result set
                    // for example if CDB_UserTables() no longer works
                    poFeatureDefn = new OGRFeatureDefn(osName);
                    poFeatureDefn->Reference();
                    poFeatureDefn->SetGeomType(wkbNone);
                }

                const char* pszAttname = poFeat->GetFieldAsString("attname");
                const char* pszType = poFeat->GetFieldAsString("typname");
                int nWidth = poFeat->GetFieldAsInteger("attlen");
                const char* pszFormatType = poFeat->GetFieldAsString("format_type");
                int bNotNull = poFeat->GetFieldAsInteger("attnotnull");
                int bIsPrimary = poFeat->GetFieldAsInteger("indisprimary");
                int iDefaultExpr = poLyr->GetLayerDefn()->GetFieldIndex("defaultexpr");
                const char* pszDefault = (iDefaultExpr >= 0 && poFeat->IsFieldSet(iDefaultExpr)) ?
                            poFeat->GetFieldAsString(iDefaultExpr) : NULL;

                if( bIsPrimary &&
                    (EQUAL(pszType, "int2") ||
                     EQUAL(pszType, "int4") ||
                     EQUAL(pszType, "int8") ||
                     EQUAL(pszType, "serial") ||
                     EQUAL(pszType, "bigserial")) )
                {
                    osFIDColName = pszAttname;
                }
                else if( strcmp(pszAttname, "created_at") == 0 ||
                         strcmp(pszAttname, "updated_at") == 0 ||
                         strcmp(pszAttname, "the_geom_webmercator") == 0)
                {
                    /* ignored */
                }
                else
                {
                    if( EQUAL(pszType,"geometry") )
                    {
                        int nDim = poFeat->GetFieldAsInteger("dim");
                        int nSRID = poFeat->GetFieldAsInteger("srid");
                        const char* pszGeomType = poFeat->GetFieldAsString("geomtyp");
                        const char* pszSRText = (poFeat->IsFieldSet(
                            poLyr->GetLayerDefn()->GetFieldIndex("srtext"))) ?
                                    poFeat->GetFieldAsString("srtext") : NULL;
                        OGRwkbGeometryType eType = OGRFromOGCGeomType(pszGeomType);
                        if( nDim == 3 )
                            eType = wkbSetZ(eType);
                        OGRCartoDBGeomFieldDefn *poFieldDefn =
                            new OGRCartoDBGeomFieldDefn(pszAttname, eType);
                        if( bNotNull )
                            poFieldDefn->SetNullable(FALSE);
                        OGRSpatialReference* poSRS = NULL;
                        if( pszSRText != NULL )
                        {
                            poSRS = new OGRSpatialReference();
                            char* pszTmp = (char* )pszSRText;
                            if( poSRS->importFromWkt(&pszTmp) != OGRERR_NONE )
                            {
                                delete poSRS;
                                poSRS = NULL;
                            }
                            if( poSRS != NULL )
                            {
                                poFieldDefn->SetSpatialRef(poSRS);
                                poSRS->Release();
                            }
                        }
                        poFieldDefn->nSRID = nSRID;
                        poFeatureDefn->AddGeomFieldDefn(poFieldDefn, FALSE);
                    }
                    else
                    {
                        OGRFieldDefn oField(pszAttname, OFTString);
                        if( bNotNull )
                            oField.SetNullable(FALSE);
                        OGRPGCommonLayerSetType(oField, pszType, pszFormatType, nWidth);
                        if( pszDefault )
                            OGRPGCommonLayerNormalizeDefault(&oField, pszDefault);

                        poFeatureDefn->AddFieldDefn( &oField );
                    }
                }
                delete poFeat;
            }

            poDS->ReleaseResultSet(poLyr);
        }
    }

    if( poFeatureDefn == NULL )
    {
        osBaseSQL.Printf("SELECT * FROM %s", OGRCARTODBEscapeIdentifier(osName).c_str());
        EstablishLayerDefn(osName, NULL);
        osBaseSQL = "";
    }

    if( osFIDColName.size() > 0 )
    {
        osBaseSQL = "SELECT ";
        osBaseSQL += OGRCARTODBEscapeIdentifier(osFIDColName);
    }
    for(int i=0; i<poFeatureDefn->GetGeomFieldCount(); i++)
    {
        if( osBaseSQL.size() == 0 )
            osBaseSQL = "SELECT ";
        else
            osBaseSQL += ", ";
        osBaseSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetGeomFieldDefn(i)->GetNameRef());
    }
    for(int i=0; i<poFeatureDefn->GetFieldCount(); i++)
    {
        if( osBaseSQL.size() == 0 )
            osBaseSQL = "SELECT ";
        else
            osBaseSQL += ", ";
        osBaseSQL += OGRCARTODBEscapeIdentifier(poFeatureDefn->GetFieldDefn(i)->GetNameRef());
    }
    if( osBaseSQL.size() == 0 )
        osBaseSQL = "SELECT *";
    osBaseSQL += " FROM ";
    osBaseSQL += OGRCARTODBEscapeIdentifier(osName);

    osSELECTWithoutWHERE = osBaseSQL;

    return poFeatureDefn;
}
예제 #15
0
OGRErr OGRCARTODBTableLayer::GetExtent( int iGeomField, OGREnvelope *psExtent, int bForce )
{
    CPLString   osSQL;

    if( bDeferedCreation && RunDeferedCreationIfNecessary() != OGRERR_NONE )
        return OGRERR_FAILURE;
    FlushDeferedInsert();

    if( iGeomField < 0 || iGeomField >= GetLayerDefn()->GetGeomFieldCount() ||
        GetLayerDefn()->GetGeomFieldDefn(iGeomField)->GetType() == wkbNone )
    {
        if( iGeomField != 0 )
        {
            CPLError(CE_Failure, CPLE_AppDefined,
                     "Invalid geometry field index : %d", iGeomField);
        }
        return OGRERR_FAILURE;
    }

    OGRGeomFieldDefn* poGeomFieldDefn =
        poFeatureDefn->GetGeomFieldDefn(iGeomField);

    /* Do not take the spatial filter into account */
    osSQL.Printf( "SELECT ST_Extent(%s) FROM %s",
                  OGRCARTODBEscapeIdentifier(poGeomFieldDefn->GetNameRef()).c_str(),
                  OGRCARTODBEscapeIdentifier(osName).c_str());

    json_object* poObj = poDS->RunSQL(osSQL);
    json_object* poRowObj = OGRCARTODBGetSingleRow(poObj);
    if( poRowObj != NULL )
    {
        json_object* poExtent = json_object_object_get(poRowObj, "st_extent");
        if( poExtent != NULL && json_object_get_type(poExtent) == json_type_string )
        {
            const char* pszBox = json_object_get_string(poExtent);
            const char * ptr, *ptrEndParenthesis;
            char szVals[64*6+6];

            ptr = strchr(pszBox, '(');
            if (ptr)
                ptr ++;
            if (ptr == NULL ||
                (ptrEndParenthesis = strchr(ptr, ')')) == NULL ||
                ptrEndParenthesis - ptr > (int)(sizeof(szVals) - 1))
            {
                CPLError( CE_Failure, CPLE_IllegalArg,
                            "Bad extent representation: '%s'", pszBox);

                json_object_put(poObj);
                return OGRERR_FAILURE;
            }

            strncpy(szVals,ptr,ptrEndParenthesis - ptr);
            szVals[ptrEndParenthesis - ptr] = '\0';

            char ** papszTokens = CSLTokenizeString2(szVals," ,",CSLT_HONOURSTRINGS);
            int nTokenCnt = 4;

            if ( CSLCount(papszTokens) != nTokenCnt )
            {
                CPLError( CE_Failure, CPLE_IllegalArg,
                            "Bad extent representation: '%s'", pszBox);
                CSLDestroy(papszTokens);

                json_object_put(poObj);
                return OGRERR_FAILURE;
            }

            // Take X,Y coords
            // For PostGis ver >= 1.0.0 -> Tokens: X1 Y1 X2 Y2 (nTokenCnt = 4)
            // For PostGIS ver < 1.0.0 -> Tokens: X1 Y1 Z1 X2 Y2 Z2 (nTokenCnt = 6)
            // =>   X2 index calculated as nTokenCnt/2
            //      Y2 index caluclated as nTokenCnt/2+1
            
            psExtent->MinX = CPLAtof( papszTokens[0] );
            psExtent->MinY = CPLAtof( papszTokens[1] );
            psExtent->MaxX = CPLAtof( papszTokens[nTokenCnt/2] );
            psExtent->MaxY = CPLAtof( papszTokens[nTokenCnt/2+1] );

            CSLDestroy(papszTokens);

            json_object_put(poObj);
            return OGRERR_NONE;
        }
    }

    if( poObj != NULL )
        json_object_put(poObj);

    if( iGeomField == 0 )
        return OGRLayer::GetExtent( psExtent, bForce );
    else
        return OGRLayer::GetExtent( iGeomField, psExtent, bForce );
}