问题描述：作为Android端的应用，在数据方面，我们希望支持各种数据表结构的Spatialite空间数据表，实现灵活的数据查询！
解决方案：
整体思路>>要查询数据表，前提是需要知道表结构，然后设定查询条件，最后进行查询，所以，我们先要得到数据库中有哪些表，然后对选择要操作的表，获取它的表结构，再进一步根据选中的条件字段获取其所有取值，最后根据字段取值查询对应的数据记录，查询到的数据由于涉及到空间数据，需要进行格式转换，然后创建要素图形，并添加要素属性，添加在worldwind地球上进行显示，并设置pick要素选择监听，对选中的要素显示相应属性信息！
第一步：获取数据库表，表结构，属性字段（空间属性特殊处理），字段值，
public ArrayList<String> selectTablesName(Context context){
try {
ArrayList<String> tables = new ArrayList<>();
connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE | Constants.SQLITE_OPEN_CREATE);
//String sql = "PRAGMA stats;";
String sql = "SELECT f_table_name FROM geometry_columns;";
Stmt stmt01 = db.prepare(sql);
//columns:table index width height
while (stmt01.step()) {
tables.add(stmt01.column_string(0));
}
db.close();
return tables;
}catch (jsqlite.Exception e){
e.printStackTrace();
}
return null;
}
public ArrayList<String> selectCoulmnsName(Context context,String selectedTableName){
try {
ArrayList<String> columns = new ArrayList<>();
connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE | Constants.SQLITE_OPEN_CREATE);
String sql = "PRAGMA table_info('"+selectedTableName+"')";
Stmt stmt01 = db.prepare(sql);
//columns :cid name type notnull dflt_value pk
while (stmt01.step()) {
//check type to get geometry column
String type = stmt01.column_string(2);
if(type.contains("POINT")||type.contains("LINE")||type.contains("POLYGON")){
geometryColumn = stmt01.column_string(1);
}else {
columns.add(stmt01.column_string(1));
}
}
db.close();
return columns;
}catch (jsqlite.Exception e){
e.printStackTrace();
}
return null;
}
public ArrayList<String> selectColumnValues(Context context,String selectedTableName,String selectedColumnName){
try {
ArrayList<String> values = new ArrayList<>();
connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE | Constants.SQLITE_OPEN_CREATE);
String sql = "SELECT "+selectedColumnName+" FROM " +selectedTableName;
Stmt stmt01 = db.prepare(sql);
while (stmt01.step()) {
values.add(stmt01.column_string(0));
}
db.close();
return values;
}catch (jsqlite.Exception e){
e.printStackTrace();
}
return null;
}
第二步：根据查询条件查询要素数据
public ArrayList<String[]> selectFeatureData(Context context,String selectedTableName,String selectedColumnName,String selectedColumnValue,ArrayList<String> columns,String geometryColumn){
try {
//columns don't include geometry column
ArrayList<String[]> featuresData = new ArrayList<>();
String[] feature = new String[columns.size()+1];
connectlocalDB(context, Constants.SQLITE_OPEN_READWRITE | Constants.SQLITE_OPEN_CREATE);
String columsString = columns.get(0);
for(int i=1;i<columns.size();i++){
columsString +=","+ columns.get(i);
}
columsString +=",ST_AsText("+geometryColumn+")";
String sql = "SELECT "+columsString+" FROM " +selectedTableName+" WHERE "+selectedColumnName+" = '"+selectedColumnValue+"';";
Stmt stmt01 = db.prepare(sql);
while (stmt01.step()) {
for(int i=0;i<columns.size()+1;i++){
feature[i]=stmt01.column_string(i);
}
featuresData.add(feature);
}
db.close();
return featuresData;
}catch (jsqlite.Exception e){
e.printStackTrace();
}
return null;
}第三步：空间数据类型转换
public class DataFormatService {
private String geomType = null;
private ArrayList<Position> positions =null;
private ArrayList<Position[]> multiPositions = null;
public String getGeomType(String geomText) {
//geomText : POINT(),POINT Z(),LineString( , , ),Polygon( , , ,),MultiPoint(( ),( )),MultiLineString(( , ,),( , ,)),MultiPolygon(( , ,),( , ,))
geomType = geomText.split(" ")[0].trim();
if (geomType.contains("(")) {
geomType = geomType.split("\\(")[0].trim();
} else {
geomType += "Z";
}
return geomType;
}
public ArrayList<Position> getPositions(String geomText) {
positions = new ArrayList<>();
getGeomType(geomText);
switch (geomType){
case "POINT":
Double[] point = pointFromText(geomText);
Position positon = new Position(point[1],point[0],0);
positions.add(positon);
break;
case "LINESTRING":
simpleGeometryFromText(geomText);
break;
case "MULTIPOINT":
simpleGeometryFromText(geomText);
break;
}
return positions;
}
public ArrayList<Position[]> getMultiPositions(String geomText) {
multiPositions = new ArrayList<>();
getGeomType(geomText);
switch (geomType){
case "POLYGON":
multiGeometryFromText(geomText);
break;
case "MULTILINESTRING":
multiGeometryFromText(geomText);
break;
case "MULTIPOLYGON":
multiGeometryFromText(geomText);
break;
}
return multiPositions;
}
public Double[] pointZFromText(String pointZText){
//pointText example : POINT Z(x y z)
Double [] point = new Double[3];
geomType = pointZText.split(" ")[0];
point[0] = Double.parseDouble(pointZText.split(" ")[1].split("\\(")[1]);
point[1] = Double.parseDouble(pointZText.split(" ")[2]);
point[2] = Double.parseDouble(pointZText.split(" ")[3].split("\\)")[0]);
return point;
}
public Double[] pointFromText(String pointText){
//pointText example : POINT(x y)
Double [] point = new Double[2];
point[0] = Double.parseDouble(pointText.split(" ")[0].split("\\(")[1]);
point[1] = Double.parseDouble(pointText.split(" ")[1].split("\\)")[0]);
return point;
}
/** geometryText LineString(,,) Polygon( ,,),MultiPoint(,,)
**/
public void simpleGeometryFromText(String simpleGeometryText){
//geometryText LineString(1 2, 3 4, 5 7) ,MultiPoint(,,)
Position position = null;
String[] first =simpleGeometryText.split("\\(");
String[] second = first[1].trim().split("\\)");
String[] third = second[0].trim().split(",");
for(int i=0;i<third.length;i++){
//匹配一个或多个空白符\\s+
String[] forth = third[i].trim().split("\\s+");
//POINT(x y)-->Position(latitude,longitude,altitude):x-->longitude,y-->latitude，注意xy与经纬度的对应关系
position= new Position(Double.parseDouble(forth[1]),Double.parseDouble(forth[0]),0);
positions.add(position);
}
}
/*** to solve multi geometry
* //Polygon(( ,, )),MultiLineString((,,),(,,)) MulitPolygon((,,),(,,))
***/
public Double[][][] multiGeometryFromText(String multiGeometryText){
//Polygon(( ,, )),MultiLineString((,,),(,,)) MulitPolygon((,,),(,,))
Position position = null;
Position[] tempositions = null;
String[] zero = multiGeometryText.split("\\(\\(");
zero = zero[1].trim().split("\\)\\)");
//*匹配0个或多个前一个字符，此处为空格符
String[] first = zero[0].trim().split("\\), *\\(");
int geometryNumber = first.length;
String[][] second = new String[geometryNumber][];
Double[][][] third = new Double[geometryNumber][][];
for(int i=0;i<geometryNumber;i++){
second[i]=first[i].trim().split(",");
int pointNumber = second[i].length;
tempositions = new Position[pointNumber];
third[i]=new Double[pointNumber][];
for(int j=0;j<pointNumber;j++){
third[i][j] = new Double[2];
for(int k=0;k<2;k++){
//匹配一个或多个空白符\\s+
third[i][j][k] = Double.parseDouble(second[i][j].trim().split("\\s+")[k]);
}
//POINT(x y)-->Position(latitude,longitude,altitude):x-->longitude,y-->latitude，注意xy与经纬度的对应关系
position = new Position(third[i][j][1],third[i][j][0],0);
tempositions[j]=position;
}
multiPositions.add(tempositions);
}
return third;
}
public String geometryToText(ArrayList<Position> positions,String type){
String geometryString = null;
switch (type){
case "POINT":
//POINT(x y)-->Position(latitude,longitude,altitude):x-->longitude,y-->latitude，注意xy与经纬度的对应关系
geometryString = "'POINT("+String.valueOf(positions.get(0).longitude)+" "+String.valueOf(positions.get(0).latitude)+")'";
break;
case "POLYLINE":
geometryString = "'LINESTRING(";
for(int i=0;i<positions.size()-1;i++){
geometryString+=String.valueOf(positions.get(i).longitude)+" "+String.valueOf(positions.get(i).latitude)+",";
}
geometryString+=String.valueOf(positions.get(positions.size()-1).longitude)+" "+String.valueOf(positions.get(positions.size()-1).latitude)+")'";
break;
case "POLYGON":
//与point，linstring在格式上有区别，是双括号
geometryString = "'POLYGON((";
for(int i=0;i<positions.size()-1;i++){
geometryString+=String.valueOf(positions.get(i).longitude)+" "+String.valueOf(positions.get(i).latitude)+",";
}
geometryString+=String.valueOf(positions.get(positions.size()-1).longitude)+" "+String.valueOf(positions.get(positions.size()-1).latitude)+"))'";
break;
}
return geometryString;
}
}四：绘制要素图形
public class GeometryCreator {
public Placemark createPlacemark(Position position, String displayName) {
final double NORMAL_IMAGE_SCALE = 3.0;
final double HIGHLIGHTED_IMAGE_SCALE = 4.0;
Placemark placemark =new Placemark(position, PlacemarkAttributes.createWithImage(ImageSource.fromResource(R.drawable.school)),displayName);
placemark.getAttributes().setImageOffset(Offset.bottomCenter()).setImageScale(NORMAL_IMAGE_SCALE).setDrawLeader(true);
placemark.setHighlightAttributes(new PlacemarkAttributes(placemark.getAttributes()).setImageScale(HIGHLIGHTED_IMAGE_SCALE));
return placemark;
}
public Path createPolyline(ArrayList<Position> position) {
Path polyline = new Path(position);
ShapeAttributes attributes = new ShapeAttributes();
attributes.setDrawVerticals(true); // display the extruded verticals
attributes.setOutlineColor(new Color(1,0,0,1));
attributes.setInteriorColor(new Color(1, 1, 1, 0.5f)); // 50% transparent white
attributes.setOutlineWidth(3);
polyline.setAttributes(attributes);
ShapeAttributes highlightAttributes = new ShapeAttributes();
highlightAttributes.setDrawVerticals(true); // display the extruded verticals
highlightAttributes.setOutlineColor(new Color(0,0,1,1));
highlightAttributes.setInteriorColor(new Color(1, 1, 1, 0.5f)); // 50% transparent white
highlightAttributes.setOutlineWidth(6);
polyline.setHighlightAttributes(highlightAttributes);
return polyline;
}
public Polygon createPolygon(ArrayList<Position> position) {
Polygon polygon = new Polygon(position);
ShapeAttributes attributes = new ShapeAttributes();
attributes.setDrawVerticals(true); // display the extruded verticals
attributes.setOutlineColor(new Color(1,0,0,1));
attributes.setInteriorColor(new Color(1, 1, 1, 0.5f)); // 50% transparent white
attributes.setOutlineWidth(3);
polygon.setAttributes(attributes);
ShapeAttributes highlightAttributes = new ShapeAttributes();
highlightAttributes.setDrawVerticals(true); // display the extruded verticals
highlightAttributes.setOutlineColor(new Color(0,0,1,1));
highlightAttributes.setInteriorColor(new Color(1, 1, 1, 0.5f)); // 50% transparent white
highlightAttributes.setOutlineWidth(6);
polygon.setHighlightAttributes(highlightAttributes);
return polygon;
}
}五、整合查询流程
btnSearch.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
//get text while it change by write instead of choose
//避免先进行了记录条件查询，再清空editview进行记录全查询时，条件依旧的情况,需要从editview重新中获取值
//而selectedTable不管是何种查询都需要，而且不允许编辑，故而不需要重新获取
//if no text in editview,we will get String ""
selectedCoulmn = columnEditText.getText().toString();
selectedValue = valueEditText.getText().toString();
try {
//select features data by given condtion
featuresData = databaseSerivce.selectFeatureData(getApplicationContext(), selectedTable, selectedCoulmn, selectedValue, columns, geometryColumn);
}catch (Exception e){
//selectedTable shouldn't be null
Toast.makeText(getApplicationContext(),"数据查询异常："+e,Toast.LENGTH_SHORT).show();
}
String geomText ,geomType = null;
ArrayList<Position> positions = null;
ArrayList<Position[]> multiPositions = null;
//feature number
int featureNum = featuresData.size();
for(int i = 0;i<featureNum;i++) {
//convert geometry data text format to digit
geomText = featuresData.get(i)[featuresData.get(i).length - 1];
//由于数据插入问题，或其他原因，空间数据为null的异常处理
if(geomText==null){
geomType = "NONE";
}
else {
geomType = dataFormatService.getGeomType(geomText);
}
//get atttibute number which used to limit loop
int attributeNum = featuresData.get(i).length-1;
Placemark placemark =null;
Path polyline = null;
Polygon polygon = null;
//draw feature by selected feature data
switch (geomType){
case "POINT":
//由一个图形组成，图形由一个点构成，点包含xy坐标
positions = dataFormatService.getPositions(geomText);
placemark = geometryCreator.createPlacemark(positions.get(0),featuresData.get(i)[0]);
//add attributes to feature except geometry attribute
attributeNum = featuresData.get(i).length-1;
for(int j=0;j<attributeNum;j++){
placemark.putUserProperty(columns.get(j),featuresData.get(i)[j]);
}
resultLayer.addRenderable(placemark);
wwd.requestRedraw();
break;
case "LINESTRING":
//由一个图形组成，图形由多个点构成，每个点包含xy坐标
positions = dataFormatService.getPositions(geomText);
polyline = geometryCreator.createPolyline(positions);
//add attributes to feature except geometry attribute
attributeNum = featuresData.get(i).length-1;
for(int j=0;j<attributeNum;j++){
polyline.putUserProperty(columns.get(j),featuresData.get(i)[j]);
}
resultLayer.addRenderable(polyline);
wwd.requestRedraw();
break;
case "POLYGON":
//由一个图形组成，图形由多个点构成，每个点包含xy坐标,POLYGON((,,)),格式与LINESTRING(,,)有所不同
multiPositions = dataFormatService.getMultiPositions(geomText);
positions = new ArrayList<>();
int pointNum = multiPositions.get(0).length;
for(int m = 0;m<pointNum;m++){
positions.add(multiPositions.get(0)[m]);
}
polygon = geometryCreator.createPolygon(positions);
//add attributes to feature except geometry attribute
attributeNum = featuresData.get(i).length-1;
for(int j=0;j<attributeNum;j++){
polygon.putUserProperty(columns.get(j),featuresData.get(i)[j]);
}
resultLayer.addRenderable(polygon);
wwd.requestRedraw();
break;
case "MULTIPOINT":
//由多个图形组成，每个图形包含一个点，每个点包含xy坐标
positions = dataFormatService.getPositions(geomText);
int pointsNumber = positions.size();
for(int k =0;k<pointsNumber;k++) {
placemark = geometryCreator.createPlacemark(positions.get(k),featuresData.get(i)[0]);
//add attributes to feature except geometry attribute
attributeNum = featuresData.get(i).length - 1;
for (int j = 0; j < attributeNum; j++) {
placemark.putUserProperty(columns.get(j), featuresData.get(i)[j]);
}
resultLayer.addRenderable(placemark);
}
wwd.requestRedraw();
break;
case "MULTILINESTRING":
//由多个图形组成，每个图形又由多个点构成，每个点包含xy坐标
multiPositions = dataFormatService.getMultiPositions(geomText);
for(int k=0;k<multiPositions.size();k++){
positions = new ArrayList<>();
int pointNumber = multiPositions.get(k).length;
for(int m = 0;m<pointNumber;m++){
positions.add(multiPositions.get(k)[m]);
}
polyline = geometryCreator.createPolyline(positions);
//add attributes to feature except geometry attribute
attributeNum = featuresData.get(i).length-1;
for(int j=0;j<attributeNum;j++){
polyline.putUserProperty(columns.get(j),featuresData.get(i)[j]);
}
resultLayer.addRenderable(polyline);
}
wwd.requestRedraw();
break;
case "MULTIPOLYGON":
//由多个图形组成，每个图形又由多个点构成，每个点包含xy坐标
multiPositions = dataFormatService.getMultiPositions(geomText);
for(int k=0;k<multiPositions.size();k++){
positions = new ArrayList<>();
int pointNumber = multiPositions.get(k).length;
for(int m = 0;m<pointNumber;m++){
positions.add(multiPositions.get(k)[m]);
}
polygon = geometryCreator.createPolygon(positions);
//add attributes to feature except geometry attribute
attributeNum = featuresData.get(i).length-1;
for(int j=0;j<attributeNum;j++){
polygon.putUserProperty(columns.get(j),featuresData.get(i)[j]);
}
resultLayer.addRenderable(polygon);
}
wwd.requestRedraw();
break;
case "NONE":
Toast.makeText(getApplicationContext(),"空间数据为空，查询失败！",Toast.LENGTH_SHORT).show();
break;
}
}
}
});
六、添加要素选中监听
public class PickObjectController extends BasicWorldWindowController {
protected Object pickedObject; // last picked object from onDown events
protected Object selectedObject; // last "selected" object from single tap
protected Context context;
public PickObjectController(Context context) {
this.context = context;
}
/**
* Assign a subclassed SimpleOnGestureListener to a GestureDetector to handle the "pick" events.
*/
protected GestureDetector pickGestureDetector = new GestureDetector(
context, new GestureDetector.SimpleOnGestureListener() {
@Override
public boolean onDown(MotionEvent event) {
pick(event); // Pick the object(s) at the tap location
return false; // By not consuming this event, we allow it to pass on to the navigation gesture handlers
}
@Override
public boolean onSingleTapUp(MotionEvent e) {
toggleSelection(); // Highlight the picked object
// By not consuming this event, we allow the "up" event to pass on to the navigation gestures,
// which is required for proper zoom gestures. Consuming this event will cause the first zoom
// gesture to be ignored. As an alternative, you can implement onSingleTapConfirmed and consume
// event as you would expect, with the trade-off being a slight delay tap response.
return false;
}
});
/**
* Delegates events to the pick handler or the native World Wind navigation handlers.
*/
@Override
public boolean onTouchEvent(MotionEvent event) {
// Allow pick listener to process the event first.
boolean consumed = this.pickGestureDetector.onTouchEvent(event);
// If event was not consumed by the pick operation, pass it on the globe navigation handlers
if (!consumed) {
// The super class performs the pan, tilt, rotate and zoom
return super.onTouchEvent(event);
}
return consumed;
}
/**
* Performs a pick at the tap location.
*/
public void pick(MotionEvent event) {
// Forget our last picked object
this.pickedObject = null;
// Perform a new pick at the screen x, y
PickedObjectList pickList = getWorldWindow().pick(event.getX(), event.getY());
// Get the top-most object for our new picked object
PickedObject topPickedObject = pickList.topPickedObject();
if (topPickedObject != null) {
this.pickedObject = topPickedObject.getUserObject();
}
}
/**
* Toggles the selected state of a picked object.
*/
public void toggleSelection() {
// Display the highlight or normal attributes to indicate the
// selected or unselected state respectively.
if (pickedObject instanceof Highlightable) {
// Determine if we've picked a "new" object so we know to deselect the previous selection
boolean isNewSelection = pickedObject != this.selectedObject;
// Only one object can be selected at time, deselect any previously selected object
if (isNewSelection && this.selectedObject instanceof Highlightable) {
((Highlightable) this.selectedObject).setHighlighted(false);
}
// Show the selection by showing its highlight attributes
((Highlightable) pickedObject).setHighlighted(isNewSelection);
this.getWorldWindow().requestRedraw();
// Track the selected object
this.selectedObject = isNewSelection ? pickedObject : null;
showAttributeDialog();//show attribute if selectObject not null
}
}
public void showAttributeDialog(){
//关键问题是如何从pickobject中得到我们设置的要素属性数据呢？
//把数据存在要素的useProperties属性中，然后就能在查询时获取了
final ArrayList<String> items = new ArrayList<>();
if(selectedObject!=null){
if(selectedObject instanceof Placemark){
Placemark selectedPlacemark = (Placemark)selectedObject;
//for getting keys ,i add getter to get useProperty in AbstractRenderable
Set<Object> keys = selectedPlacemark.getUserProperties().keySet();
Iterator<Object> iterator = keys.iterator();
while (iterator.hasNext()) {
String key =iterator.next().toString();
String value = selectedPlacemark.getUserProperty(key).toString();
items.add( key+":"+ value);
}
}else if(selectedObject instanceof Path){
Path selectedPolyline = (Path) selectedObject;
Set<Object> keys = selectedPolyline.getUserProperties().keySet();
Iterator<Object> iterator = keys.iterator();
while (iterator.hasNext()) {
String key =iterator.next().toString();
String value = selectedPolyline.getUserProperty(key).toString();
items.add( key+":"+ value);
}
}else if(selectedObject instanceof Polygon){
Polygon selectedPolygon = (Polygon)selectedObject;
Set<Object> keys = selectedPolygon.getUserProperties().keySet();
Iterator<Object> iterator = keys.iterator();
while (iterator.hasNext()) {
String key =iterator.next().toString();
String value = selectedPolygon.getUserProperty(key).toString();
items.add( key+":"+ value);
}
}
AlertDialog.Builder builder = new AlertDialog.Builder(context);
builder.setTitle("要素属性信息")
.setAdapter(new ArrayAdapter<String>(context, R.layout.array_item, items), new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
Toast.makeText(context,"你选中了《"+items.get(which)+"》",Toast.LENGTH_SHORT).show();
}
})
.setPositiveButton("确定", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
Toast.makeText(context,"单击了［确定］按钮",Toast.LENGTH_SHORT).show();
}
})
.setNegativeButton("取消", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
Toast.makeText(context,"单击了［取消］按钮",Toast.LENGTH_SHORT).show();
}
})
.create()
.show();
}
}
}细节问题记录：
1、对WorldWindAndroid的AbstractRenderable的源码修改，添加getUserProperties方法，获取userProperties（Map）属性，以获取完整键，值信息，进行属性显示！
2、对象实例化时机：在对象地址不改变的情况下，不要频繁更改数据，并在其他地方重复使用该对象，造成数据均为当前值的情况，例如Position p = new Position();for(){p.set(x,y,z); positions.add(p);}，解决方法是在每次更改对象数据时进行实例化，可以new，也可以直接赋值，都会改变内存指向！
3、正则表达式：在进行字符串数据解析时需要用到
问题：Spatialite中ST_AsText（geom）得到的空间数据字符串中“，”后会跟一个空格（如LINESTRING((x y,x y , x y), (x y, x y, x y))）
4、关键问题，POLYGON的字符串格式为POLYGON((,,))，而不是POLYGON(,,)，所以在解析时和MULTILINESTRING((,,),(,,))的解析方式一致，在DataFormService中需要注意！
5、存入数据库的数据是按照POINT(x y)来存储的，读取后生成Position(latitude,longitude,altitude)时，latitude-->y，longitude-->x，所以需要注意顺序，不要直接按位置对应，在数据插入时也要注意，x-->longitude，y-->latitude！
"\\), *\\(" :*匹配0个或多个前一个字符，此处为空格符"\\s+":匹配一个或多个空白符\\s+
项目地址：https://gitee.com/lq_project/CampusProjectRepository/tree/master/CampusProject/test
928
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
