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 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:

name='1'

Query all 3D polylines on layer index 0:

name='4' and layerindex=0

Query red single-line text:

name='13' and color=-16776961

Query entity with objectid 9BE:

objectid='9BE'

Query all text (single-line, multi-line, attribute definition, block attribute):

name='13' or name='12' or name='26' or name='27'

Query block entities whose objectId starts with EA, 10D, or 19A:

objectid like 'EA_%'  or objectid like '10D_%'  or objectid like '19A_%'  

Query lines with length 10:

name='1' and n6 = 10

Query circles with radius 5:

name='7' and n7 = 5

Conditional Query

 /**
 * 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<any>;
/**
 * 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

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
})

显示代码

全屏显示

Point Query

 /**
 * 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<any>;
  
/**
 * 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 (opens new window)

Rectangle Query

 /**
 * 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<any>;
   

/**
 * 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 (opens new window)

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 (opens new window)

Paginated Query

Use paginated query when there is a lot of data.

Example: Query all line segments (lines, 2D/3D polylines) in the map

 // 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
        }
    }
}