--- title: SQL Query source: https://vjmap.com/en/guide/dataQuery.html --- # SQL Query When a CAD drawing is opened in `geometry render` mode, the server converts CAD data to spatial data and stores it in a `spatial database`. You can run custom `sql` queries against this data from the frontend. ## Backend Table Structure See [Server-side conditional query and expression query](/en/guide/svrStyleVar.html#conditional-query) for details. For the backend table structure, use the condition part of the SQL statement for queries. You only need to write the part after `where`. Table structure: | Field Name | Type | Description | |:---|:---|:---| | id | integer | id | | objectid | text | Entity id (block objectid: block id_referenced block def id1_referenced block def id2_entity id; group objectid: group id$entity id_element index; table: table id@entity id_element index; others: id_element index) | | layerindex | integer | Layer index | | name | text | Entity type value, e.g. "1" for AcDbLine; see "Supported CAD entity types" below | | color | int | Color (from RGB, e.g. black 0x000000; with alpha 0xFF000000, unsigned 0xff000000<<0 = -16777216) | | data | blob | Geometry data | | s1 | text | Linetype | | s2 | text | Extended dictionary data | | s3 | text | Coordinate data; format varies by entity | | s4 | text | String attribute data; format varies by entity | | s5 | text | String attribute data; format varies by entity | | n1 | float | Color index | | n2 | float | Linetype scale | | n3 | float | Line width | | n4 | float | Transparency | | n5 | float | Numeric attribute; format varies by entity | | n6 | float | Numeric attribute; format varies by entity | | n7 | float | Numeric attribute; format varies by entity | | n8 | float | Numeric attribute; format varies by entity | | n9 | float | Numeric attribute; format varies by entity | | n10 | float | Numeric attribute; format varies by entity | | geom | GEOMETRY | Entity bounding box | ## Table Structure and Field Meanings | id | objectid | layerindex | name | color | s3 | s4 | s5 | n5 | n6 | n7 | n8 | n9 | n10 | |:---------|:--------|:----|:----------|:----------|:-------------|:--------------|:----|:----|:----|:----------|:----------|:---------|:---------| | DB id int | Entity id text | Layer index int | Type value text | Color int | text | text | text | numeric | numeric | numeric | numeric | numeric | numeric | | | | | 1 (AcDbLine) | | points | | | thickness | length | | | | | | | | | 2 (AcDbPolyline) | | points | bulge | data3d | area | isclosed | linetypeScale | thickness | elevation | | | | | | 3 (AcDb2dPolyline) | | points | | | area | isclosed | polyType | thickness | elevation | | | | | | 4 (AcDb3dPolyline) | |points | | | area | isclosed | polyType | | | | | | | | 5 (AcDbSpline) | | points | | | area | isclosed | isFit | fitTol |degree | | | | | | 6 (AcDbArc) | | center | | | area | isclosed | radius | startAngle | endAngle | thickness | | | | | 7 (AcDbCircle) | | center | | | area | isclosed | radius | thickness | | | | | | | 8 (AcDbEllipse) | | center | majorAxis | | area | isclosed | radius | startAngle |endAngle | | | | | | 9 (AcDbCurve) | | | | | area | isclosed | | | | | | | | | 10 (AcDbBlockReference) | | positon | attributeDef | blockname | rotate | scaleX | scaleY | scaleZ | | | | | | | 11 (AcDbHatch) | | points |patternName | | elevation | patternAngle | patternScale | | | | | | | | 12 (AcDbMText) | | location | contents | text | height | width | rotate | textHeight | actualHeight | actualWidth | | | | | 13 (AcDbText) | | location |text | | height | rotate | horzMode | | | | | | | | 14 (AcDbShape) | | positon | normal | | rotate | size | | | | | | | | | 15 (AcDbRasterImage) | | origin | | | | | | | | | | | | | 16 (AcDbWipeout) | | 16 | | | | | | | | | | | | | | 17 (AcDb2LineAngularDimension) | | | | | | | | | | | | | | | 18 (AcDb3PointAngularDimension) | | | | | | | | | | | | | | | 19 (AcDbAlignedDimension) | | | | | | | | | | | | | | | 20 (AcDbArcDimension) | | | | | | | | | | | | | | | 21 (AcDbDiametricDimension) | | | | | | | | | | | | | | | 22 (AcDbOrdinateDimension) | | | | | | | | | | | | | | | 23 (AcDbRadialDimension) | | | | | | | | | | | | | | | 24 (AcDbRadialDimensionLarge) | | | | | | | | | | | | | | | 25 (AcDbRotatedDimension) | | | | | | | | | | | | | | | 26 (AcDbAttributeDefinition) | | location |text | contents | height | rotate | horzMode | | | | | | | | 27 (AcDbAttribute) | | location |text | contents | height | rotate | horzMode | | | | | | | | 28 (AcDbTable) | | positon | attributeDef | contents | rotate | scaleX | scaleY | scaleZ | | | | | | | 29 (AcDbMLeader) | | location | contents | text | height | width | rotate | textHeight |actualHeight | actualWidth | | | | | 30 (AcDbRegion) | | points | indices | data3d | area | perimeter | | | | | | | | | 31 (AcDbPolyFaceMesh) | | points | indices | data3d | | | | | | | | | | | 32 (AcDbPolygonMesh) | | points |indices | data3d | | | | | | | | | | | 33 (AcDbSurface) | | points | indices | data3d | | | | | | | | | | | 34 (AcDb3dSolid) | | points | indices| data3d | | | | | | | | | | | 35 (AcDbFace) | | points | indices | data3d | | | | | | | ## SQL Examples **Write only the part after `where`** Query all lines in the map: ```js name='1' ``` Query all 3D polylines on layer index 0: ```js name='4' and layerindex=0 ``` Query red single-line text: ```js name='13' and color=-16776961 ``` Query entity with objectid 9BE: ```js objectid='9BE' ``` Query all text (single-line, multi-line, attribute definition, block attribute): ```js name='13' or name='12' or name='26' or name='27' ``` Query block entities whose objectId starts with EA, 10D, or 19A: ```js objectid like 'EA_%' or objectid like '10D_%' or objectid like '19A_%' ``` Query lines with length 10: ```js name='1' and n6 = 10 ``` Query circles with radius 5: ```js name='7' and n7 = 5 ``` ## Conditional Query ```ts /** * Conditional query for entities * @param param Parameters * @param cb Callback for each point in result; if returns null, default handling is used */ conditionQueryFeature(param: IConditionQueryFeatures, cb?: (point: [number, number]) => [number, number] | null | undefined): Promise; /** * Conditional query parameters */ export interface IConditionQueryFeatures extends IQueryBaseFeatures { /** Condition. */ condition: string; /** Bounds or point coordinate array */ bounds?: [number, number, number, number] | [number, number][]; /** Get intersections when bounds is point array; default false */ isGetIntersections?: boolean; /** Record start position. */ beginpos?: number; /** Return geometry; for performance, when realgeom is false and count > 1, only bounding box per entity; when count is 1, returns real GeoJSON; when realgeom is true, returns GeoJSON per entity */ includegeom?: boolean; /** Return real entity geometry GeoJSON. Used with includegeom. See includegeom. */ realgeom?: boolean; /** Contain vs intersect; true = contain, false = intersect; default false (when bounds is provided for range query) */ isContains?: boolean; } ``` Example: Query all text in the map ```js let query = await svc.conditionQueryFeature({ condition: `name='13' or name='12' or name='26' or name='27'`, // Write only the condition after where; see "Server-side conditional query and expression query" for fields fields: "", geom: true, limit: 100000 // Large value to get all circles; default 100 if omitted }) ``` 
// --Get all text in map and highlight--
// js code
// Create map service with service URL and token
let svc = new vjmap.Service(env.serviceUrl, env.accessToken)
// Open map
let res = await svc.openMap({
    mapid: env.exampleMapId, // Map ID (ensure it exists; upload new graphic to create)
    mapopenway: vjmap.MapOpenWay.GeomRender, // Open with geometry render mode
    style: vjmap.openMapDarkStyle() // Use dark style when div has dark background
})
if (res.error) {
    message.error(res.error)
}
// Get map bounds
let mapExtent = vjmap.GeoBounds.fromString(res.bounds);
// Build coordinate system
let prj = new vjmap.GeoProjection(mapExtent);
// Create map object
let map = new vjmap.Map({
    container: 'map', // container ID
    style: svc.rasterStyle(), // Raster tile style
    center: prj.toLngLat(mapExtent.center()), // Center
    zoom: 2,
    renderWorldCopies: false
});
// Attach service and projection
map.attach(svc, prj);
// Fit map to full extent
map.fitMapBounds();
let polygonLayer;
const getAllTexts = async ()=> {
    if (polygonLayer) return; // Skip if already drawn
    // Entity type ID to name mapping
    const { entTypeIdMap } = await svc.getConstData();
    const getTypeNameById = name => Object.keys(entTypeIdMap).find(e => entTypeIdMap[e] === name);
    let polygons = [];
    let queryTextEntTypeId = getTypeNameById("AcDbText"); // Single-line text
    let queryMTextEntTypeId = getTypeNameById("AcDbMText"); // Multi-line text
    let queryAttDefEntTypeId = getTypeNameById("AcDbAttributeDefinition"); // Attribute definition text
    let queryAttEntTypeId = getTypeNameById("AcDbAttribute"); // Attribute text
    let query = await svc.conditionQueryFeature({
        condition: `name='${queryTextEntTypeId}' or name='${queryMTextEntTypeId}' or name='${queryAttDefEntTypeId}' or name='${queryAttEntTypeId}'`, // Write only the condition after where; see "Server-side conditional query and expression query" for fields
        fields: "",
        geom: true,
        limit: 100000 // Large value to get all circles; default 100 if omitted
    })
    if (query.error) {
        message.error(query.error);
        return;
    } else {
        if (query.recordCount > 0) {
            for(let i = 0; i < query.recordCount; i++) {
                let bounds = map.getEnvelopBounds(query.result[i].envelop);
                let clr = vjmap.entColorToHtmlColor(query.result[i].color); // Entity color to HTML color
                polygons.push({
                    points: map.toLngLat(bounds.toPointArray()),
                    properties: {
                        name: query.result[i].objectid,
                        color: clr,
                        text: query.result[i].text
                    }
                });
            }
        }
    }
    polygonLayer = new vjmap.Polygon({
        data: polygons,
        fillColor: ['case', ['to-boolean', ['feature-state', 'hover']], 'yellow', '#f0f'],
        fillOpacity: ['case', ['to-boolean', ['feature-state', 'hover']], 0.6, 0.4],
        fillOutlineColor: ['get', 'color'],
        isHoverPointer: true,
        isHoverFeatureState: true
    });
    polygonLayer.addTo(map);
    polygonLayer.hoverFeatureState(e => {
        if (e.features) {
            map.logInfo(`Text: name ${e.features[0].properties.name}, value ${e.features[0].properties.text}`)
        }
    })
}
getAllTexts()


## Point Query ```ts /** * Point query for entities * @param param Parameters * @param cb Callback for each point in result; if returns null, default handling is used */ pointQueryFeature(param: IPointQueryFeatures, cb?: (point: [number, number]) => [number, number] | null | undefined): Promise; /** * Point query parameters */ export interface IPointQueryFeatures extends IQueryBaseFeatures { /** Query X coordinate. */ x: number; /** Query Y coordinate. */ y: number; /** Pixel size. */ pixelsize?: number; /** Condition. */ condition?: string; /** Max geometry bytes. */ maxGeomBytesSize?: number; /** Geo length per pixel; if set, overrides zoom-based calculation. */ pixelToGeoLength?: number; } /** * Query parameters */ export interface IQueryBaseFeatures { /** Current zoom level. */ zoom?: number; /** Map ID (empty = current open map). */ mapid?: string; /** Map version (empty = current open version). */ version?: string; /** Layer name (empty = current open map layer). */ layer?: string; /** Max records. */ limit?: number; /** Field list, comma-separated, e.g. "name,objectid" */ fields?: string; /** Include geometry. */ geom?: boolean; /** GeoJSON simplify tolerance in Mercator distance; default 0. E.g. for ~1px error at zoom 10: map.pixelToGeoLength(1, 10) * vjmap.Projection.EQUATORIAL_SEMIPERIMETER * 2 / map.getGeoBounds(1.0).width() */ simplifyTolerance?: boolean; /** Enable cache (for memory-opened maps). */ useCache?: boolean; /** Query returns Mercator by default; toMapCoordinate=true returns CAD map coordinates. */ toMapCoordinate?: boolean; } ``` Example [Click to select CAD entity on overlay map](https://vjmap.com/demo/#/demo/map/service/22hightselentity) ## Rectangle Query ```ts /** * Rectangle query for entities * @param param Parameters * @param cb Callback for each point in result; if returns null, default handling is used */ rectQueryFeature(param: IRectQueryFeatures, cb?: (point: [number, number]) => [number, number] | null | undefined): Promise; /** * Rectangle query parameters */ export interface IRectQueryFeatures extends IQueryBaseFeatures { /** Query X1. (If x1,y1,x2,y2 all omitted, queries full map extent) */ x1?: number; /** Query Y1. */ y1?: number; /** Query X2. */ x2?: number; /** Query Y2. */ y2?: number; /** Condition. */ condition?: string; /** Max geometry bytes. */ maxGeomBytesSize?: number; } /** * Query parameters */ export interface IQueryBaseFeatures { /** Current zoom level. */ zoom?: number; /** Map ID (empty = current open map). */ mapid?: string; /** Map version (empty = current open version). */ version?: string; /** Layer name (empty = current open map layer). */ layer?: string; /** Max records. */ limit?: number; /** Field list, comma-separated, e.g. "name,objectid" */ fields?: string; /** Include geometry. */ geom?: boolean; /** GeoJSON simplify tolerance in Mercator distance; default 0. E.g. for ~1px error at zoom 10: map.pixelToGeoLength(1, 10) * vjmap.Projection.EQUATORIAL_SEMIPERIMETER * 2 / map.getGeoBounds(1.0).width() */ simplifyTolerance?: boolean; /** Enable cache (for memory-opened maps). */ useCache?: boolean; /** Query returns Mercator by default; toMapCoordinate=true returns CAD map coordinates. */ toMapCoordinate?: boolean; } ``` Example [Auto-generate CAD engineering section from data](https://vjmap.com/demo/#/demo/map/comprehensive/03datatodwgmap?lineNumber=496&column=25&endColumn=40) For `rectangle query`, you can also use `conditionQueryFeature` with `bounds` set to a rectangle `[number, number, number, number]`. ## Polygon Query Use `conditionQueryFeature` for `polygon query` with `bounds` set to a coordinate array `Array<[number, number]>`. Example [Polygon select entities](https://vjmap.com/demo/#/demo/map/service/22hightselentitypoly) ## Paginated Query Use paginated query when there is a lot of data. Example: Query all line segments (lines, 2D/3D polylines) in the map ```js // Find all line segments in map const getMapHVLines = async () => { // Find all lines, 2D/3D polylines let queryEntTypes = ['AcDbLine', 'AcDbPolyline', 'AcDb2dPolyline', 'AcDb3dPolyline']; let cond = queryEntTypes.map(t => `name='${getTypeNameById(t)}'`).join(' or '); // SQL condition let result = []; let beginPos = 0; // Query start position // Use paginated query for large datasets while(true) { let query = await svc.conditionQueryFeature({ condition: cond, // Write only the condition after where; see "Server-side conditional query and expression query" for fields fields: "objectid,points,envelop", // Only id and coordinates beginpos: beginPos, // Start position limit: 100000 // 100k per batch }); beginPos += query.result.length; // Advance start position result.push(...query.result || []); if (result.length >= query.recordCount) break; } result = result.filter(e => { let points = e.points.split(";"); if (points.length !=2 ) return false; e.geoStart = vjmap.GeoPoint.fromString(points[0]); delete e.geoStart.z;// Ignore z e.geoEnd = vjmap.GeoPoint.fromString(points[1]); delete e.geoEnd.z;// Ignore z if (e.geoStart.x > e.geoStart.x) { // Swap let temp = e.geoStart; e.geoStart = e.geoEnd; e.geoEnd = temp; } e.startPoint = e.geoStart.toString(); e.endPoint = e.geoEnd.toString(); if (e.startPoint == e.endPoint) { // Same point return false; } let line = points.map(e=>vjmap.geoPoint(e.split(","))) let isVLine = vjmap.isZero(line[0].x - line[1].x);// Vertical let isHLine = vjmap.isZero(line[0].y - line[1].y);// Horizontal if (!(isVLine || isHLine)) return false; // Must be horizontal or vertical e.isHorzLine = isHLine; return true }) return result; } // Entity type ID to name mapping const { entTypeIdMap } = await svc.getConstData(); const getTypeNameById = name => { for(let id in entTypeIdMap) { if (entTypeIdMap[id] == name) { return id } } } ```