import requests from pub.utils import Coords from pub.utils import Geotext from pub.queryMoi import QueryElevation #as httpMoi from pub.extactJSON import ParserMOI as moiParser class FeatureTableParser(): def sql_sel(self): sqlWhere='' #gid >=1000 and gid <=5000 if(len(self.where)): sqlWhere=' where {}'.format(self.where) sqlGid= ( 'select gid from '+ '( select gid,count(*) numofPoint from ( '+ ' select gid,(dp).path[2] SN,ST_Z((dp).geom) zpoint'+ ' from (select gid,ST_DumpPoints({}) dp from {}."{}") allpoint '.format(self.geom,self.schema,self.table)+ ') allzero {} group by gid '.format(sqlWhere)+ ') numofzero {} '.format(self.limit) ) # 'select gid from ( '+ # 'select gid,count(*) numofPoint from ( '+ # 'select gid,ST_Z(ST_PointN( {}, generate_series(1, ST_NPoints({})))) zpoint '.format(self.geom,self.geom)+ # ' from {}."{}"'.format(self.schema,self.table)+ # ') allzero {} group by gid '.format(sqlWhere)+ # ') numofzero {}'.format(self.limit) ) sqlQuery=( 'SELECT gid, did, tid, oid,gtype, pt,ST_X(geom3) x,ST_Y(geom3) y,ST_Z(geom3) z '+ 'from ( select gid,did,tid,oid,gtype,numpt, COALESCE((alldp).path[2],1) pt,(alldp).geom geom3 from ('+ ' select gid,did,tid,oid ,GeometryType({}) gtype,ST_NPoints({}) numpt,'.format(self.geom,self.geom) + ' ST_DumpPoints({}) alldp from {}."{}"'.format(self.geom,self.schema,self.table)+ ' ) atpoint where gid in ( {} )) allpt order by gid ,pt '.format(sqlGid)) # 'SELECT gid, did, tid, oid,gtype, pt,ST_X(geom3) x,ST_Y(geom3) y,ST_Z(geom3) z '+ # ' from ( SELECT gid, did, tid, oid, GeometryType({}) gtype, '.format(self.geom) + # ' ST_NumPoints({}) , generate_series(1, ST_NPoints({})) pt, '.format(self.geom,self.geom)+ # ' ST_PointN( {}, generate_series(1, ST_NPoints({}))) geom3 '.format(self.geom,self.geom)+ # ' FROM {}."{}" where gid in ( '.format(self.schema,self.table)+ # sqlGid+ # ' )) allpt order by gid ,pt ') print(sqlQuery) return sqlQuery #( #'SELECT gid, did, tid, oid,gtype, pt,ST_X(geom3) x,ST_Y(geom3) y,ST_Z(geom3) z ' + #'from ( SELECT gid, did, tid, oid, GeometryType(geom) gtype, '+ #'ST_NumPoints(geom) , generate_series(1, ST_NPoints(geom)) pt, '+ #'ST_PointN( geom, generate_series(1, ST_NPoints(geom))) geom3 '+ #' FROM {}."{}" where gid in (select gid from {}."{}" {} ) ) allpt order by gid ,pt '.format(self.schema,self.table,self.schema,self.table,sqlwhere) ) def sql_upd(self,col_geom='geom'): return 'UPDATE {}."{}" set {}=ST_GeomFromEWKT(\'SRID={};{}\') where gid={}'.format(self.schema,self.table,col_geom,self.dbSRS,'{}','{}') #wkt,id) def colnames(self): return 'gid, did, tid, oid, gtype, pt, x, y, z' def colforgeotext(self,clos): return [(cols[0],cols[4],cols[5],cols[6],cols[7],cols[8])] def __init__(self,tablename,schema='gis3d',limit=100,where='zpoint=0',dbSRS='3826',geom='geom'): self.geom=geom self.schema=schema self.table=tablename self.where=where self.limit='' self.dbSRS=dbSRS if(limit>0): self.limit=' limit {}'.format(limit) class Updateev0(): def add(self,cols): self.geotext.add(cols[0],cols[4],cols[5],cols[6],cols[7],cols[8]) #update DB def done(self,msg): self.geotext.done(msg) def updateto0(self,allpts): newpts=[]; for onept in allpts: newpts.append([onept[0],onept[1],0]) return newpts def upDb(self,pp4): #call ext #print(pp4[0],pp4[2]) #print(pp4) if(self.fnAfter != None): self.fnAfter(self.sql_upd(), self.buildWKT(pp4[1],pp4[2]),pp4[0],self.table) def buildWKT(self,gtype,wktpt): resultwkt='' if(gtype=='POINT'): resultwkt='POINT({})'.format(wktpt) if(gtype=='POLYGON'): resultwkt='POLYGON(({}))'.format(wktpt) if(gtype=='LINESTRING'): resultwkt='LINESTRING({})'.format(wktpt) return resultwkt def sql_sel(self): return self.ft.sql_sel() def sql_upd(self): return self.ft.sql_upd(self.desgeom) def __init__(self,tablename,fnAfter=None,fnErr=None,limit=100,dbSRS='3826',desgeom='geom',srcgeom='geom'): print('init') self.table=tablename self.fnAfter=fnAfter self.fnErr=fnErr self.geotext=Geotext(self.updateto0,self.upDb) self.ft=FeatureTableParser(tablename,limit=limit,dbSRS=dbSRS,geom=srcgeom) self.desgeom=desgeom class GetMoiEvBy84(): def add(self,cols): self.geotext.add(cols[0],cols[4],cols[5],cols[6],cols[7],cols[8]) #updatedb def done(self,msg): self.geotext.done(msg) #querybyimoi def procmoi(self,allpts): newpts=[]; #print('coming',allpts) for onept in allpts: if(len(onept)>2): newpts.append(self.towgs84(onept[0],onept[1],onept[2])) else: if(self.fnErr!=None): resultTxt='moi->{} / {}'.format(onept,allpts) self.fnErr('[error] {}, id->{}',resultTxt,'?','moi') print('error@moi@{},row={}'.format(onept,allpts) ) #print(self.makempoint(newpts)) return newpts #parsertoWgs84 def towgs84(self,dx,dy,dz): return self.coords.crs_t97tow84(dx,dy,dz) def makempoint(self,allpts): resultText='MULTIPOINT(({}))' resultOne='' for onept in allpts: if ( len(resultOne)>0): resultOne+=',' resultOne+= ' '.join ('%s' %id for id in onept) return resultText.format(resultOne) def printit(self,pp4): print(pp4) def queryBy(self,pp4): #queryMoi result=self.httpMoi.fromMoi("MULTIPOINT({})".format(pp4[2])) if(result.status_code == requests.codes.ok): resultjs=moiParser.toJSON(result.text) resultmoiarray=moiParser.moiJsonToArray(resultjs) #print('moi=',resultmoiarray) #print('org=',pp4) if (len(resultmoiarray)==0): if(self.fnErr!=None): resultTxt='moi->{},our->{}'.format(resultjs,'gid:{},to84:({}),from97:({})'.format(pp4[0],pp4[2], pp4[3])) self.fnErr('[error] {}, id->{}',resultTxt,pp4[0],self.table) print('error@parser@{},id={}'.format(self.table,pp4[0]) ) return if (self.fnAfter != None): self.fnAfter(self.sql_upd(), self.buildWKT( pp4[1], self.makeSqlWktWithEv(pp4[3],resultmoiarray)) , pp4[0 ],self.table) def checkDistinctSquare(self,x0,y0,x1,y1): return (x0-x1)*(x0-x1)+(y0-y1)*(y0-y1) def makeSqlWktWithEv(self,xyorg,xymoi): lenOfxyorg=len(xyorg) #print('len',lenOfxyorg) newPts=[] for idxPt in range(lenOfxyorg): diff= self.checkDistinctSquare( float(xyorg[idxPt][0]),float(xyorg[idxPt][1]), float(xymoi[idxPt][0]),float(xymoi[idxPt][1])) if(diff>1): print( 'diff xy({}> 1m)'.format( diff )) newPts.append([xyorg[idxPt][0],xyorg[idxPt][1],xymoi[idxPt][2]]) resultText='' for pt in newPts: if(len(resultText)>0): resultText+=',' thisrow= ' '.join ('%s' %id for id in pt) resultText+= thisrow #print('wkt->',resultText) return (resultText) def upDb(self,pp4): #call ext #print(pp4[0],pp4[2]) if(self.fnAfter != None): self.fnAfter(self.sql_upd(), self.buildWKT(pp4[1],pp4[2]),pp4[0]) def buildWKT(self,gtype,wktpt): resultwkt='' if(gtype=='POINT'): resultwkt='POINTZ({})'.format(wktpt) if(gtype=='POLYGON'): resultwkt='POLYGONZ(({}))'.format(wktpt) if(gtype=='LINESTRING'): resultwkt='LINESTRINGZ({})'.format(wktpt) return resultwkt def sql_sel(self): return self.ft.sql_sel() def sql_upd(self): return self.ft.sql_upd(self.desgeom) def __init__(self,tablename,fnAfter=None,fnErr=None,limit=100,dbSRS='3826',desgeom='geom',srcgeom='geom'): print('init') self.coords=Coords(default97=dbSRS) self.httpMoi=QueryElevation() # self.geotext=Geotext(self.procmoi,self.queryBy) self.table=tablename self.fnAfter=fnAfter self.fnErr=fnErr self.ft=FeatureTableParser(tablename,limit=limit,dbSRS=dbSRS,geom=srcgeom) self.desgeom=desgeom